﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agro.Models;
using System.Data;
using System.Diagnostics;
using Agro.Library;
using SN.Core;
using SN.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Web.Script.Serialization;
using System.Runtime.Serialization.Json;
using System.Text.RegularExpressions;


namespace Agro.Services
{
    public class ReportService : BaseService, IReportService
    {
        public string[] splitText = new string[] { "", "&nbsp;", "&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" };

        public T_Report GetReportProperty(string reportid)
        {
            T_Report report = BaseService.DB.FromSql(string.Format("select * from t_report where report_id='{0}'", reportid)).ToSingle<T_Report>();
            return report;
        }
        public DataTable GetZBSJData(T_SubmitInfo submitInfo)
        {
            string sql = "select zbsj_zbbh,zbsj_value from t_zbsj where zbsj_reportid='{0}' and zbsj_dwid='{1}' and ";
            switch (submitInfo.SubmitInfo_Cycle)
            {
                case "01":
                    sql += " zbsj_year='" + submitInfo.SubmitInfo_Year + "' and zbsj_month='" + submitInfo.SubmitInfo_Month + "'";
                    break;
                case "02":
                    sql += " zbsj_year='" + submitInfo.SubmitInfo_Year + "' and zbsj_month='" + submitInfo.SubmitInfo_Month + "'";
                    break;
                case "03":
                    sql += " zbsj_year='" + submitInfo.SubmitInfo_Year + "'";
                    break;
            }
            sql += " and zbsj_cycle='{2}' and submitinfoid='{3}'";
            return DB.FromSql(string.Format(sql, submitInfo.SubmitInfo_ReportID, submitInfo.SubmitInfo_OrganizationID, submitInfo.SubmitInfo_Cycle, submitInfo.SubmitInfo_ID)).ToDataTable();

        }

        public string GetReprotFormat(int submitInfoId, CurrentUser user)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();//用来计算报表加载时间，开始
            //报表的表格样式和内容
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoId);

            List<T_ReportContents> dataList = BaseService.DB.FromSql(string.Format("select * from T_ReportContents where Contents_ReportId='{0}' order by contents_row,contents_col", submitInfo.SubmitInfo_ReportID)).ToList<T_ReportContents>().ToList();
            T_Report report = GetReportProperty(submitInfo.SubmitInfo_ReportID);//报表属性,取得行高，列宽等。
            DataTable ReportDataDt = GetZBSJData(submitInfo);//取得报表 的表格真实数据
            DataColumn[] keys = new DataColumn[1];//设置dataTable主键
            keys[0] = ReportDataDt.Columns[0];
            ReportDataDt.PrimaryKey = keys;

            if (report.Report_ColWidth == null) return "";
            string[] arrcolwidths = report.Report_ColWidth.Split(',');//取得报表每列的宽度
            int collength = arrcolwidths.Length;//取得报表列数
            string[] arrrowheights = report.Report_RowHeight.Split(',');//取得报表每行的高度
            bool isCurrOrgId = submitInfo.SubmitInfo_OrganizationID == user.UserInfo.User_OrganizationID.ToString();

            StringBuilder Builder = new StringBuilder();
            Builder.Append("<form id='formdetail'>\n");
            Builder.AppendFormat("<input type='hidden' id='reportid' name='reportid' value='{0}' />\n", submitInfo.SubmitInfo_ReportID);
            Builder.AppendFormat("<input type='hidden' id='dwid' name='dwid' value='{0}' />\n", submitInfo.SubmitInfo_OrganizationID);
            Builder.AppendFormat("<input type='hidden' id='year' name='year' value='{0}' />\n", submitInfo.SubmitInfo_Year);
            Builder.AppendFormat("<input type='hidden' id='month' name='month' value='{0}' />\n", submitInfo.SubmitInfo_Month);
            Builder.AppendFormat("<input type='hidden' id='day' name='day' value='{0}' />\n", submitInfo.SubmitInfo_Day);
            Builder.AppendFormat("<input type='hidden' id='cycle' name='cycle' value='{0}'/>\n", report.Report_Cycle);
            Builder.AppendFormat("<input type='hidden' id='SubmitInfo_ID' name='SubmitInfo_ID' value='{0}'/>\n", submitInfoId);
            //如果不是报表所在用户打开报表，显示为只读状态
            Builder.AppendFormat("<input type='hidden' id='state' name='state' value='{0}'/>\n", isCurrOrgId ? submitInfo.SubmitInfo_State : "1");

            Builder.Append("</form>\n");
            Builder.Append("<table id='TableMain' style='position:absolute;width:{0}px;left:0px;table-layout:fixed;z-index:100;display:none;'>\n");
            Builder.Append("<colgroup>\n");
            int allwidth = 0;
            for (int m = 0; m < collength; m++)//设置报表每列宽度
            {
                Builder.AppendFormat("<col style='width:{0}px'>\n", arrcolwidths[m]);
                allwidth = allwidth + int.Parse(arrcolwidths[m]);
            }
            Builder.Append("</colgroup>\n");
            Builder = new StringBuilder(string.Format(Builder.ToString(), allwidth));

            int curRow = 0;
            int j = 0;
            int tRow = 0, tCol = 0, RowSpan = 0, ColSpan = 0, Inden = 0;
            string Text = string.Empty;
            string ZB = string.Empty;
            string cssText = string.Empty;
            string ZBValue = string.Empty;
            string Formula = string.Empty;


            foreach (T_ReportContents t_report in dataList)//dataList中每一行数据，对应表格中的一个单元格，设置每个单元格样式
            {
                ZB = string.Empty;
                ZBValue = string.Empty;
                Formula = string.Empty;
                tRow = t_report.CONTENTS_ROW;// int.Parse(dr["CONTENTS_ROW"].ToString());
                tCol = t_report.CONTENTS_COL;//int.Parse(dr["CONTENTS_COL"].ToString());
                RowSpan = t_report.CONTENTS_ROWSPAN.Value;
                ColSpan = t_report.CONTENTS_COLSPAN.Value;
                Formula = string.IsNullOrEmpty(t_report.CONTENTS_Formula) ? "" : "Formula='" + t_report.CONTENTS_Formula + "'";//指标公式
                ZB = (t_report.CONTENTS_IsEdit == "1" || Formula != "") ? t_report.CONTENTS_TEXT : "";//如果该单元格允许编辑或者是计算公式，则返回指标编号

                DataRow dr = ReportDataDt.Rows.Find(ZB.Replace("[Calc]", ""));//查找对应指标编号的指标数据
                ZBValue = dr == null ? "" : dr["ZBSJ_VALUE"].ToString();
                Text = t_report.CONTENTS_IsEdit == "1" || Formula != "" ? ZBValue : t_report.CONTENTS_TEXT;//单元格显示的文本，如果是指标，显示指标值，否则显示标题值。
                Inden = Convert.ToInt32(t_report.CONTENTS_Indentation);//缩进
                cssText = "";
                cssText = "font-family:" + t_report.CONTENTS_FontName + ";font-size:" + t_report.CONTENTS_FontSize + "pt;" + (t_report.CONTENTS_FontBold == null ? "" : t_report.CONTENTS_FontBold + ";") + "overflow: hidden;" + t_report.CONTENTS_H + ";" + t_report.CONTENTS_V + ";" + t_report.CONTENTS_BorderStyle;
                //有公式就设置个背景色
                if (!string.IsNullOrEmpty(Formula))
                {
                    cssText += ";background-color:#DCDCDC";
                }
                //数据类型，如果是数字附加到td的属性上
                string datatypeatt = string.Empty;
                if (!string.IsNullOrEmpty(t_report.CONTENTS_Format))
                {
                    if (t_report.CONTENTS_Format.StartsWith("1"))
                    {
                        //附加小数位数，同时有这个属性表示是数值类型
                        datatypeatt = " digits='" + t_report.CONTENTS_Format.Split(',')[1] + "'";
                    }
                }
                if (tRow > 0 && curRow > 0 && curRow != tRow)
                {
                    Builder.Append("</tr>\n");
                }
                if (curRow != tRow)
                {
                    Builder.AppendFormat("<tr style='height:{4}px'>\n  <td  rowspan={0} colspan={1} style='{3}' row={5} col={6} isEdit={7} ZB='{9}'>{8}{2}</td>\n", RowSpan, ColSpan, Text, cssText, arrrowheights[j], t_report.CONTENTS_ROW, t_report.CONTENTS_COL, t_report.CONTENTS_IsEdit, splitText[Inden], ZB);
                    j++;
                }
                else
                {
                    Builder.AppendFormat(" <td rowspan={0} colspan={1}  style='{3}' row={4} col={5} isEdit={6} ZB='{8}'{9}>{7}{2}</td>\n", RowSpan, ColSpan, Text, cssText, t_report.CONTENTS_ROW, t_report.CONTENTS_COL, isCurrOrgId ? t_report.CONTENTS_IsEdit : "0", splitText[Inden], ZB, datatypeatt);
                }
                curRow = tRow;
            }
            Builder.Append("</tr>\n</table>\n");
            sw.Stop();//用来计算报表加载时间，结束
            Builder.Append("<span style='font-size:12px;'>总运行时间：" + sw.ElapsedMilliseconds + "毫秒</span>\n");
            return Builder.ToString();
        }

        public int GetCount(T_ZBSJ zbsj)
        {
            string sql = "";
            switch (zbsj.ZBSJ_Cycle)
            {
                case "01":
                    sql = "select count(1) from t_zbsj where submitinfoid='{7}' and  zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}'   and zbsj_cycle='{6}'";
                    break;
                case "02":
                    sql = "select count(1) from t_zbsj where submitinfoid='{7}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}'   and zbsj_cycle='{6}'";
                    break;
                case "03":
                    sql = "select count(1) from t_zbsj where submitinfoid='{7}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_cycle='{6}'";
                    break;
                default:
                    sql = "select count(1) from t_zbsj where submitinfoid='{7}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}' and zbsj_day='{5}' and zbsj_cycle='{6}'";
                    break;
            }
            sql = string.Format(sql, zbsj.ZBSJ_ReportID, zbsj.ZBSJ_ZBBH, zbsj.ZBSJ_DWID, zbsj.ZBSJ_YEAR, zbsj.ZBSJ_MONTH, zbsj.ZBSJ_DAY, zbsj.ZBSJ_Cycle, zbsj.SubmitInfoID);
            int count = Convert.ToInt32(DB.FromSql(sql).ToScalar());
            return count;
        }

        public int SaveZBSJ(T_ZBSJ zbsj, CurrentUser user)
        {
            if (user.UserInfo.User_OrganizationID.ToString() != zbsj.ZBSJ_DWID)
            {
                return -1;
            }
            int count = GetCount(zbsj);//取得指标个数
            string sql = "";
            int i = 0;
            if (count > 0)
            {
                switch (zbsj.ZBSJ_Cycle)
                {
                    case "01":
                        sql = "update   t_zbsj  set zbsj_value='{7}'  where submitinfoid='{8}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}'   and zbsj_cycle='{6}'";
                        break;
                    case "02":
                        sql = "update   t_zbsj  set zbsj_value='{7}'  where submitinfoid='{8}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}'  and zbsj_cycle='{6}'";
                        break;
                    case "03":
                        sql = "update   t_zbsj  set zbsj_value='{7}'  where submitinfoid='{8}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}'   and zbsj_cycle='{6}'";
                        break;
                    default:
                        sql = "update   t_zbsj  set zbsj_value='{7}'  where submitinfoid='{8}' and zbsj_reportid='{0}' and zbsj_zbbh='{1}' and zbsj_dwid='{2}' and zbsj_year='{3}' and zbsj_month='{4}' and zbsj_day='{5}' and zbsj_cycle='{6}'";
                        break;
                }
                sql = string.Format(sql, zbsj.ZBSJ_ReportID, zbsj.ZBSJ_ZBBH, zbsj.ZBSJ_DWID, zbsj.ZBSJ_YEAR, zbsj.ZBSJ_MONTH, zbsj.ZBSJ_DAY, zbsj.ZBSJ_Cycle, zbsj.ZBSJ_Value, zbsj.SubmitInfoID);
                i = DB.FromSql(sql).Execute();
            }
            else
            {
                i = DB.Save<T_ZBSJ>(zbsj);
            }
            return i;
        }

        public DataTable ReportGetList(QueryBase query, WhereClip wclip)
        {
            Field[] fields = { 
                              T_SubmitInfo._.All,T_Organization._.Organization_Name,T_Organization._.Organization_ID, T_Report._.Report_ID, T_Report._.Report_Name
                             };
            var section = DB.From<T_SubmitInfo>()
                .LeftJoin<T_Report>(T_SubmitInfo._.SubmitInfo_ReportID == T_Report._.Report_ID)
                .LeftJoin<T_Organization>(T_SubmitInfo._.SubmitInfo_OrganizationID == T_Organization._.Organization_ID)
                .Where(wclip);
            var dt = section.ToTablePage(query, fields);
            return dt;
        }

        public DataTable GetReportStateList(QueryBase query, string wclip, string wSubmitinfoclip, string reportid)
        {
            string sql = @"select Organization_ID,Organization_Code,Organization_Name,SubmitInfo_Cycle,SubmitInfo_CreateDate,SubmitInfo_EndDate,SubmitInfo_LazyDate,
SubmitInfo_CreatorName,isnull(SubmitInfo_State,'0') as SubmitInfo_State,SubmitInfo_SubmitDate,SubmitInfo_Sjly,SubmitInfo_ID,SubmitInfo_ReportID,Report_Name,Report_Cycle
 from T_Organization left join T_SubmitInfo on (" + wSubmitinfoclip + ") left join T_Report on (Report_ID='" + reportid + "') where " + wclip + "";
                            ;
            System.Data.DataTable dt = GetPageData(sql, query);
          
            return dt;
        }


        public int updateState(string SubmitInfoId, string state, CurrentUser user, out string msg)
        {
            // state 0 为取消上报
            // state 1 上报
            msg = string.Empty;
            //此处需要逻辑验证

            //截止时间检查
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == SubmitInfoId);
            T_Organization organization = GetEntity<T_Organization>(T_Organization._.Organization_ID == submitInfo.SubmitInfo_OrganizationID);

            WhereClip where = WhereClip.All;
            WhereClip whereZBSJ = null;
            switch (submitInfo.SubmitInfo_Cycle)
            {
                case "01":
                case "02":
                    where = where && T_SubmitInfo._.SubmitInfo_Month == submitInfo.SubmitInfo_Month;
                    whereZBSJ = whereZBSJ && T_ZBSJ._.ZBSJ_MONTH == submitInfo.SubmitInfo_Month;
                    break;
            }
            if (DateTime.Parse(submitInfo.SubmitInfo_LazyDate) < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
            {
                msg = "报表已过截止上报期，操作失败！";
                return 0;
            }
            if (state == "1")
            { //上报 
                //检查是否为本单位
                if (user.UserInfo.User_OrganizationID.ToString() != submitInfo.SubmitInfo_OrganizationID)
                {
                    msg = "不能操作非本单位的数据";
                    return 0;
                }
                if (user.UserInfo.User_OrganizationID == 1)//省厅产业办的报表不需要上报。
                {
                    msg = "河南省农业厅产业办的报表不需要上报！";
                    return 0;
                }
                List<string> msgList = new List<string>();
                T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitInfo.SubmitInfo_ReportID);

                //模板报表才检查逻辑公式
                if (report.Report_Sfmb == 1)
                {
                    //判断有无指标数据，没有指标数据，不能上报。
                    whereZBSJ = whereZBSJ && T_ZBSJ._.ZBSJ_Cycle == submitInfo.SubmitInfo_Cycle && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year
                        && T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID && T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID && T_ZBSJ._.SubmitInfoID == SubmitInfoId;
                    IList<T_ZBSJ> childZBSJList = DB.From<T_ZBSJ>()
                        .Where(whereZBSJ).ToList();

                    if (childZBSJList.Count == 0)
                    {
                        msg = "没有指标数据，不能上报！";
                        return 0;
                    }

                   //如果下发类型是2，即只有企业填报，管理部门只能看的报表，可能会有复合校验公式如">=a+b"
                    if (report.Report_Xflx == "2")
                    {
                        var errList = CheckFhZBValue(SubmitInfoId, out msgList);
                        if (errList.Count > 0)
                        {
                            msg = "error";
                            return 2;
                        }
                    }
                    else
                    {
                        var errList = CheckZBValue(SubmitInfoId, out msgList);
                        if (errList.Count > 0)
                        {
                            msg = "error";
                            return 2;
                        }
                    }


                    
                    
                }
                else
                {
                    Type temp = null;
                    int icountRow = 0;
                    //判断非模板类报表，是否有数据
                    if (System.Configuration.ConfigurationManager.AppSettings["NSYWQWReportID"] == submitInfo.SubmitInfo_ReportID)
                    { temp = typeof(T_5000W); icountRow = CountRow<T_5000W>(SubmitInfoId); }
                    if (System.Configuration.ConfigurationManager.AppSettings["XXNYQYJBReportID"] == submitInfo.SubmitInfo_ReportID)
                    { temp = typeof(T_XXNYQYJB); icountRow = CountRow<T_XXNYQYJB>(SubmitInfoId); }
                    if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYJBReportID"] == submitInfo.SubmitInfo_ReportID)
                    { temp = typeof(T_NYCYHLTQYJB); icountRow = CountRow<T_NYCYHLTQYJB>(SubmitInfoId); }
                    if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYNBReportID"] == submitInfo.SubmitInfo_ReportID)
                    { temp = typeof(T_NYCYHLTQYNB1); icountRow = CountRow<T_NYCYHLTQYNB1>(SubmitInfoId); }
                    //icountRow = CountRow(temp, SubmitInfoId);
                    if (icountRow == 0)
                    {
                        msg = "报表中没有数据，不能上报！";
                        return 0;
                    }
                }
            }
            else
            {

                //取消上报，只能由上级部门取消下级部门。不能自己取消
                if (user.UserInfo.User_OrganizationID.ToString() != organization.Organization_ParentID.ToString()&&!user.UserInfo.IsAdmin())
                {
                    msg = "只能取消本单位下级部门的数据";
                    return 0;
                }
                //取消上报 判断上级单位已经汇总过
                int icount = DB.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ReportID == submitInfo.SubmitInfo_ReportID &&
                                                    T_SubmitInfo._.SubmitInfo_OrganizationID == organization.Organization_ParentID &&
                                                    T_SubmitInfo._.SubmitInfo_Year == submitInfo.SubmitInfo_Year &&
                                                    T_SubmitInfo._.SubmitInfo_Cycle == submitInfo.SubmitInfo_Cycle && where);
                if (icount > 0)
                {
                    msg = "当期已存在上级单位数据，不允许操作!";
                    return 0;
                }

            }
            string nowdate = state == "1" ? DateTime.Now.ToString("yyyy-MM-dd") : null;//上报日期或取消上报日期 ，当取消上报时，将上报日期设为null
            return DB.Update<T_SubmitInfo>(new Field[] { T_SubmitInfo._.SubmitInfo_State, T_SubmitInfo._.SubmitInfo_SubmitDate }, new object[] { state, nowdate }, T_SubmitInfo._.SubmitInfo_ID == SubmitInfoId);
        }

        public DataTable ReprotList(QueryBase query, WhereClip wclip)
        {
            Field[] fields = { T_Report._.All };
            var section = DB.From<T_Report>().Select(fields).Where(wclip);
            var dt = section.ToTablePage(query, fields);
            return dt;
        }

        /// <summary>
        /// 汇总下级单位的数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="dwid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="day"></param>
        /// <param name="cycle"></param>
        public string CollectReport(string reportId, string dwid, string year, string month, string jd, string day, CurrentUser currUser, string org)
        {
            string Msg = string.Empty;
            string[] childorg;

            T_SubmitInfo submitInfo = null;
            //默认的截止时间
            DateTime defDeadLine = DateTime.Now;

            #region Modified 2013-06-21 根据所传入报表ID 进行判断该单位是否已经有数据，如果已经有，则不再生成新的，否则需要生成新的报表数据
            T_Report Report = DB.From<T_Report>().Where(T_Report._.Report_ID == reportId).ToSingle();
            WhereClip where = null;
            WhereClip whereChild = null;

            switch (Report.Report_Cycle)
            {
                case "01": //月报re
                    where = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                    T_SubmitInfo._.SubmitInfo_Year == year &&
                                                    T_SubmitInfo._.SubmitInfo_Month == month &&
                                                    T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle &&
                                                    T_SubmitInfo._.SubmitInfo_OrganizationID == dwid;
                    whereChild = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                    T_SubmitInfo._.SubmitInfo_Year == year &&
                                                    T_SubmitInfo._.SubmitInfo_Month == month &&
                                                    T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle;

                    submitInfo = DB.Single<T_SubmitInfo>(where);
                    T_DeadlineConfig monthDeadline = DB.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "MONTH");
                    defDeadLine = new DateTime(int.Parse(year), int.Parse(month), 1).AddDays(monthDeadline.Value);
                    //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                    if ((monthDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                    { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(monthDeadline.FormatDay)); }
                    break;
                case "02"://季报
                    where = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                   T_SubmitInfo._.SubmitInfo_Year == year &&
                                                   T_SubmitInfo._.SubmitInfo_Month == month &&
                                                   T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle &&
                                                   T_SubmitInfo._.SubmitInfo_OrganizationID == dwid;

                    whereChild = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                   T_SubmitInfo._.SubmitInfo_Year == year &&
                                                   T_SubmitInfo._.SubmitInfo_Month == month &&
                                                   T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle;

                    submitInfo = DB.Single<T_SubmitInfo>(where);
                    T_DeadlineConfig seasonDeadline = DB.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "SEASON");
                    int seasonToMonth = jd == "01" ? 1 : jd == "02" ? 4 : jd == "03" ? 7 : 10;
                    defDeadLine = new DateTime(int.Parse(year), seasonToMonth, 1).AddDays(seasonDeadline.Value);
                    //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                    if ((seasonDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                    { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(seasonDeadline.FormatDay)); }
                    break;
                case "03": //年报
                    where = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                   T_SubmitInfo._.SubmitInfo_Year == year &&
                                                   T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle &&
                                                   T_SubmitInfo._.SubmitInfo_OrganizationID == dwid;

                    whereChild = T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                   T_SubmitInfo._.SubmitInfo_Year == year &&
                                                   T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle;

                    submitInfo = DB.Single<T_SubmitInfo>(where);
                    T_DeadlineConfig yearDeadline = DB.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "YEAR");
                    //农业厅要求年报在次年2月25日前上报，如果是闰年，应该要在配置的默认上报延迟天数上加一天
                    int delayday = yearDeadline.Value;
                    if (int.Parse(year) % 4 == 0 && int.Parse(year) % 100 == 0 || int.Parse(year) % 400 == 0)
                    {
                        delayday += 1;
                    }
                    defDeadLine = new DateTime(int.Parse(year), 1, 1).AddDays(delayday);
                    //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                    if ((yearDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                    { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(yearDeadline.FormatDay)); }
                    break;
                default:
                    break;
            }
            if (submitInfo == null)
            {

            }
            else
            {
                //判断报表状态
                if (submitInfo.SubmitInfo_State == "1")
                {
                    return "当期报表已经上报，不允许进行汇总操作!";
                }
            }
            #endregion
            //先取下级的所有单位
            //先将本级部门替换为空。如本级为1,则
            org = "," + org;//将第一位添加个逗号。为了下面的替换方便，替换方式为",dwid,",如",21,"将替换为","
            org = org.Replace("," + dwid.Trim() + ",", ",");
            org = org.Substring(1);//去除第一位处的逗号
            if (org.Length > 0)//如果选择的有下级部门，择按选择的下级部门汇总，如果没有选择，再汇总所有下级部门
            {
                org = org.Substring(0, org.Length - 1);//去掉最后面的逗号
                childorg = org.Split(',');
            }
            else
            {
                IList<T_Organization> childOrgList = DB.From<T_Organization>().Where(T_Organization._.Organization_ParentID == dwid).ToList();
                if (childOrgList.Count == 0) return "没有下级单位！";
                int[] temp = childOrgList.Select(m => m.Organization_ID).ToArray();
                childorg = Array.ConvertAll<int, string>(temp, delegate(int n) { return n.ToString(); });
            }

            //取本期间提交的单位
            IList<T_SubmitInfo> submitOrgList = DB.From<T_SubmitInfo>()
                .Where(T_SubmitInfo._.SubmitInfo_OrganizationID.In(childorg) &&
                whereChild && T_SubmitInfo._.SubmitInfo_State == "1").ToList();

            if (submitOrgList.Count == 0) return "下级单位没有数据";

            //如果表头存在，则修改，不存在则新增。
            if (submitInfo == null)
            {
                submitInfo = new T_SubmitInfo();
                submitInfo.SubmitInfo_ReportID = Report.Report_ID;
                submitInfo.SubmitInfo_Year = year;
                submitInfo.SubmitInfo_Month = month; //存季度或月份
                submitInfo.SubmitInfo_Day = "";
                submitInfo.SubmitInfo_OrganizationID = dwid;//部门
                submitInfo.SubmitInfo_State = "0"; //未报送
                submitInfo.SubmitInfo_Cycle = Report.Report_Cycle;
                submitInfo.SubmitInfo_CreateDate = DateTime.Now.ToString("yyyy-MM-dd");
                submitInfo.SubmitInfo_EndDate = defDeadLine.ToString("yyyy-MM-dd");
                submitInfo.SubmitInfo_LazyDate = defDeadLine.ToString("yyyy-MM-dd");
                submitInfo.SubmitInfo_Sjly = "数据汇总";

                submitInfo.SubmitInfo_CreatorID = currUser.UserInfo.User_ID;
                submitInfo.SubmitInfo_CreatorName = currUser.UserInfo.User_Name;

                submitInfo.Detach();
                DB.Save<T_SubmitInfo>(submitInfo);
            }
            else
            {
                submitInfo.SubmitInfo_Sjly = "数据汇总";
                submitInfo.SubmitInfo_CreatorID = currUser.UserInfo.User_ID;
                submitInfo.SubmitInfo_CreatorName = currUser.UserInfo.User_Name;
                submitInfo.Attach();
                DB.Save<T_SubmitInfo>(submitInfo);
            }

            #region  //判断是否为5000W的表 该表需单独处理
            if (System.Configuration.ConfigurationManager.AppSettings["NSYWQWReportID"] == reportId)
            {
                using (DbTrans trans = DB.BeginTrans())
                {
                    try
                    {
                        //先删除当前明细记录
                        trans.Delete<T_5000W>(T_5000W._.SubmitInfoID == submitInfo.SubmitInfo_ID);

                        IList<T_5000W> childDataList = trans.From<T_5000W>().Where(T_5000W._.SubmitInfoID.In(submitOrgList.Select(m => m.SubmitInfo_ID).ToArray())).ToList();
                        foreach (var item in childDataList)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                return Msg;
            }
            #endregion

            #region //判断是否为河南省规模以上休闲农业企业主要经济指标季报表a 该表需单独处理
            if (System.Configuration.ConfigurationManager.AppSettings["XXNYQYJBReportID"] == reportId)
            {
                using (DbTrans trans = DB.BeginTrans())
                {
                    try
                    {
                        //先删除当前明细记录
                        trans.Delete<T_XXNYQYJB>(T_XXNYQYJB._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        IList<T_XXNYQYJB> childDataList = trans.From<T_XXNYQYJB>().Where(T_XXNYQYJB._.SubmitInfoID.In(submitOrgList.Select(m => m.SubmitInfo_ID).ToArray())).ToList();
                        foreach (var item in childDataList)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                return Msg;
            }
            #endregion

            #region //判断是否为农业产业化龙头企业季报表 该表要进行单独处理
            if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYJBReportID"] == reportId)
            {
                using (DbTrans trans = DB.BeginTrans())
                {
                    try
                    {
                        //先删除当前明细记录
                        trans.Delete<T_NYCYHLTQYJB>(T_NYCYHLTQYJB._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        IList<T_NYCYHLTQYJB> childDataList = trans.From<T_NYCYHLTQYJB>().Where(T_NYCYHLTQYJB._.SubmitInfoID.In(submitOrgList.Select(m => m.SubmitInfo_ID).ToArray())).ToList();
                        foreach (var item in childDataList)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                return Msg;
            }
            #endregion

            #region 龙头企业年报的特殊处理...
            if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYNBReportID"] == reportId)
            {
                using (DbTrans trans = DB.BeginTrans())
                {
                    try
                    {
                        //先删除当前明细记录
                        trans.Delete<T_NYCYHLTQYNB1>(T_NYCYHLTQYNB1._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB2>(T_NYCYHLTQYNB2._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB3>(T_NYCYHLTQYNB3._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB4>(T_NYCYHLTQYNB4._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB5>(T_NYCYHLTQYNB5._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB6>(T_NYCYHLTQYNB6._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB7>(T_NYCYHLTQYNB7._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        int[] childOrgids = submitOrgList.Select(m => m.SubmitInfo_ID).ToArray();
                        var dataList1 =
                            trans.From<T_NYCYHLTQYNB1>().Where(T_NYCYHLTQYNB1._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList1)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList2 =
                            trans.From<T_NYCYHLTQYNB2>().Where(T_NYCYHLTQYNB2._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList2)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList3 =
                            trans.From<T_NYCYHLTQYNB3>().Where(T_NYCYHLTQYNB3._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList3)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList4 =
                            trans.From<T_NYCYHLTQYNB4>().Where(T_NYCYHLTQYNB4._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList4)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList5 =
                            trans.From<T_NYCYHLTQYNB5>().Where(T_NYCYHLTQYNB5._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList5)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList6 =
                            trans.From<T_NYCYHLTQYNB6>().Where(T_NYCYHLTQYNB6._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList6)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        var dataList7 =
                            trans.From<T_NYCYHLTQYNB7>().Where(T_NYCYHLTQYNB7._.SubmitInfoID.In(childOrgids)).ToList();
                        foreach (var item in dataList7)
                        {
                            item.ID = 0;
                            item.SubmitInfoID = submitInfo.SubmitInfo_ID;
                            item.Detach();
                            trans.Save(item);
                        }
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                return Msg;
            }
            #endregion


            WhereClip whereChildZBSJ = null;
            switch (Report.Report_Cycle)
            {
                case "01":
                case "02":
                    whereChildZBSJ = T_ZBSJ._.ZBSJ_YEAR == year && T_ZBSJ._.ZBSJ_MONTH == month;
                    break;
                case "03":
                    whereChildZBSJ = T_ZBSJ._.ZBSJ_YEAR == year;
                    break;
            }
            whereChildZBSJ = whereChildZBSJ && T_ZBSJ._.ZBSJ_Cycle == Report.Report_Cycle;
            IList<T_ZBSJ> childZBSJList = DB.From<T_ZBSJ>()
                .Where(T_ZBSJ._.ZBSJ_DWID.In(submitOrgList.Select(m => m.SubmitInfo_OrganizationID).ToArray()) &&
                whereChildZBSJ && T_ZBSJ._.ZBSJ_ReportID == Report.Report_ID).ToList();

            if (childZBSJList.Count == 0) return "没有指标数据";
            var group = childZBSJList.GroupBy(m => m.ZBSJ_ZBBH);
            List<T_ZBSJ> dataList = new List<T_ZBSJ>();
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    WhereClip whereDelete = null;
                    if (Report.Report_Cycle == "01" || Report.Report_Cycle == "02")
                    {
                        whereDelete = T_ZBSJ._.ZBSJ_YEAR == year && T_ZBSJ._.ZBSJ_MONTH == month;
                    }
                    else if (Report.Report_Cycle == "03")
                        whereDelete = T_ZBSJ._.ZBSJ_YEAR == year;
                    whereDelete = whereDelete && T_ZBSJ._.ZBSJ_DWID == dwid && T_ZBSJ._.ZBSJ_ReportID == reportId && T_ZBSJ._.SubmitInfoID == submitInfo.SubmitInfo_ID;
                    trans.Delete<T_ZBSJ>(whereDelete);

                    foreach (var item in group)
                    {
                        T_ZBSJ zbsj = new T_ZBSJ();
                        zbsj.ZBSJ_DWID = dwid;
                        zbsj.SubmitInfoID = submitInfo.SubmitInfo_ID;
                        zbsj.ZBSJ_ReportID = Report.Report_ID;
                        zbsj.ZBSJ_YEAR = year;
                        zbsj.ZBSJ_MONTH = month;
                        zbsj.ZBSJ_DAY = day;
                        zbsj.ZBSJ_Cycle = Report.Report_Cycle;
                        zbsj.ZBSJ_ZBBH = item.Key;
                        zbsj.ZBSJ_Value = item.Sum(m => ParseDecimal(m.ZBSJ_Value)).ToString();
                        if (zbsj.ZBSJ_Value == "0") zbsj.ZBSJ_Value = "";
                        //新增
                        zbsj.Detach();
                        trans.Save(zbsj);
                    }

                    //提交
                    trans.Commit();
                    //如果数据保存完 ，进行公式的处理.把公式计算放在trans.Commit()的原因是，只有提交了，才可以计算刚保存的数据。
                    CalcFormulaZBValue(submitInfo.SubmitInfo_ID, currUser);
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Msg = ex.Message;
                }
            }
            return Msg;
        }

        private decimal ParseDecimal(string val)
        {
            if (string.IsNullOrEmpty(val)) return 0m;
            decimal result = 0m;
            decimal.TryParse(val, out result);
            return result;
        }

        #region 计算指标公式...
        private decimal CalcZBValue(string zbbh, IList<T_ZBZD> zbzdList,
          IList<T_ZBSJ> zbsjList, T_SubmitInfo submitInfo, string cycle, int level)
        {

            T_ZBZD zbzd = zbzdList.First(m => m.ZBZD_ZBBH == zbbh);
            //如果是非公式指标并且已存在数据直接返回数据
            if (string.IsNullOrEmpty(zbzd.ZBZD_Formula))
            {
                if (zbsjList.Count(m => m.ZBSJ_ZBBH == zbbh) > 0)
                {
                    T_ZBSJ zbsj = zbsjList.First(m => m.ZBSJ_ZBBH == zbbh);
                    return ParseDecimal(zbsj.ZBSJ_Value);
                }
                return 0;
            }
            else
            { //有公式但是有指标值，也直接返回
                T_ZBSJ zbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == zbbh);
                //TODO: 此处需注释掉
                //if (level <= 1) { //最末级的指标如果有值直接返回
                //    if (zbsj != null && !string.IsNullOrEmpty(zbsj.ZBSJ_Value))
                //        return ParseDecimal(zbsj.ZBSJ_Value);
                //}
            }

            string formulaText = zbzd.ZBZD_Formula.Trim().Replace(" ", "");
           
            //此处重新处理公式，可处理+ - * /，通过将公式中的指标替换成对应的数据，最终得到计算结果20141128姜添加

            MatchCollection Matches =Regex.Matches(formulaText, @"\[Calc\]\d{1,}");//\[Calc\]中，因为 []都是转义字符，所以要加\
            
            //将匹配到的所有指标都替换成值
            foreach (Match NextMatch in Matches)  
            {    
             
                formulaText=formulaText.Replace(NextMatch.Value.ToString(), CalcZBValue(NextMatch.Value.ToString().Replace("[Calc]", ""), zbzdList, zbsjList, submitInfo, cycle, level - 1).ToString() );
               
            }  

            //如果公式中出现/0，则直接返回值0，因为除以0将报错
            if (formulaText.IndexOf("/0") > 0)
                formulaText = "0";

             decimal sum = 0;
            DataTable dt = new DataTable();
            decimal tempnum = 0;
            string CalcResault = "";

            //通过DataTable.Compute来计算字符串中的表达式结果。
            CalcResault = dt.Compute(formulaText, "false").ToString();
            if (!string.IsNullOrEmpty(CalcResault) && decimal.TryParse(CalcResault, out tempnum))
                sum = Math.Round(decimal.Parse(CalcResault), 2);
            else
                sum=0;
            
           ///上面内容替换下面内容 
            
            //string[] paras = formulaText.Split('+');//暂时只处理 加
            //decimal sum = 0;
            //foreach (var item in paras)
            //{
            //    if (item.Contains("[Calc]"))
            //    {
            //        sum += CalcZBValue(item.Replace("[Calc]", ""), zbzdList, zbsjList, submitInfo, cycle, level - 1);
            //    }
            //    else
            //    {
            //        sum += decimal.Parse(item.Trim());
            //    }
            //}



            if (zbsjList.Count(m => m.ZBSJ_ZBBH == zbbh) > 0)
            { //已存在则更新
                T_ZBSJ zbsj = zbsjList.First(m => m.ZBSJ_ZBBH == zbbh);
                if (zbsj.ZBSJ_Value != sum.ToString())
                {
                    if (string.IsNullOrEmpty(zbsj.ZBSJ_Value) && sum == 0)
                    {
                    }
                    else
                    {
                        zbsj.ZBSJ_Value = sum == 0 ? "" : sum.ToString();
                        WhereClip where = T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID &&
                            T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID && T_ZBSJ._.SubmitInfoID == submitInfo.SubmitInfo_ID;
                        if (cycle == "01" || cycle == "02")
                        {
                            where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year &&
                            T_ZBSJ._.ZBSJ_MONTH == submitInfo.SubmitInfo_Month;
                        }
                        else if (cycle == "03")
                            where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year;
                        where = where && T_ZBSJ._.ZBSJ_ZBBH == zbbh;
                        DB.Update<T_ZBSJ>(T_ZBSJ._.ZBSJ_Value, sum.ToString(),
                            where);
                    }
                }
            }
            else
            {
                if (sum != 0)
                {
                    T_ZBSJ model = new T_ZBSJ();
                    model.ZBSJ_ReportID = submitInfo.SubmitInfo_ReportID;
                    model.SubmitInfoID = submitInfo.SubmitInfo_ID;
                    model.ZBSJ_ZBBH = zbbh;
                    model.ZBSJ_DWID = submitInfo.SubmitInfo_OrganizationID;
                    model.ZBSJ_YEAR = submitInfo.SubmitInfo_Year;
                    model.ZBSJ_MONTH = submitInfo.SubmitInfo_Month ?? "";
                    model.ZBSJ_DAY = "";
                    model.ZBSJ_Cycle = cycle;
                    model.ZBSJ_Value = sum.ToString();
                    model.Detach();
                    DB.Save(model);
                    zbsjList.Add(model);
                }
            }
            return sum;
        }

        public void CalcFormulaZBValue(int submitInfoId, CurrentUser user)
        {
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoId);
            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitInfo.SubmitInfo_ReportID);

            WhereClip where = T_ZBSJ._.ZBSJ_ReportID == report.Report_ID && T_ZBSJ._.ZBSJ_Cycle == report.Report_Cycle &&
                T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID && T_ZBSJ._.SubmitInfoID == submitInfoId;
            string cycle = report.Report_Cycle;
            if (cycle == "01" || cycle == "02")
            {
                where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year && T_ZBSJ._.ZBSJ_MONTH == submitInfo.SubmitInfo_Month;
            }
            else if (cycle == "03")
                where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year;

            IList<T_ZBSJ> zbsjList = DB.From<T_ZBSJ>().Where(where).ToList();
            IList<T_ZBZD> zbzdList = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == report.Report_ID).ToList();
            List<T_ZBZD> formulaZBZDList = zbzdList.Where(m => !string.IsNullOrEmpty(m.ZBZD_Formula)).ToList();
            //先找出没有被别的单元格引用的指标

            List<T_ZBZD> norefZBList = new List<T_ZBZD>();
            foreach (var item in formulaZBZDList)
            {
                if (formulaZBZDList.Count(m => item.ZBZD_Formula.Contains(m.ZBZD_ZBBH)) == 0)
                {
                    norefZBList.Add(new T_ZBZD
                    {
                        ZBZD_Formula = item.ZBZD_Formula,
                        ZBZD_ZBBH = item.ZBZD_ZBBH,
                        ZBZD_ReportID = item.ZBZD_ReportID,
                        ZBZD_Remark = item.ZBZD_Remark
                    });
                }
            }
            //计算这些指标
            foreach (var item in norefZBList)
            {
                CalcZBValue(item.ZBZD_ZBBH, zbzdList, zbsjList, submitInfo, cycle, 1);
            }
            //再找上层指标，即引用这些没有被引用的指标
            List<T_ZBZD> secondZBList = new List<T_ZBZD>();
            foreach (var item in formulaZBZDList)
            {
                foreach (var zb in norefZBList)
                {
                    if (item.ZBZD_Formula.Contains(zb.ZBZD_ZBBH) &&
                        norefZBList.Count(m => m.ZBZD_ZBBH == item.ZBZD_ZBBH) == 0)
                    {
                        secondZBList.Add(new T_ZBZD
                        {
                            ZBZD_Formula = item.ZBZD_Formula,
                            ZBZD_ZBBH = item.ZBZD_ZBBH,
                            ZBZD_ReportID = item.ZBZD_ReportID,
                            ZBZD_Remark = item.ZBZD_Remark
                        });
                        break;
                    }
                }
            }
            //计算二层指标
            foreach (var item in secondZBList)
            {
                CalcZBValue(item.ZBZD_ZBBH, zbzdList, zbsjList, submitInfo, cycle, 2);
            }
            //找第三层 即最上级，到此层级结束，不再向上找
            List<T_ZBZD> thridZBList = new List<T_ZBZD>();
            foreach (var item in formulaZBZDList)
            {
                foreach (var zb in secondZBList)
                {
                    if (item.ZBZD_Formula.Contains(zb.ZBZD_ZBBH) &&
                        secondZBList.Count(m => m.ZBZD_ZBBH == item.ZBZD_ZBBH) == 0)
                    {
                        thridZBList.Add(new T_ZBZD
                        {
                            ZBZD_Formula = item.ZBZD_Formula,
                            ZBZD_ZBBH = item.ZBZD_ZBBH,
                            ZBZD_ReportID = item.ZBZD_ReportID,
                            ZBZD_Remark = item.ZBZD_Remark
                        });
                        break;
                    }
                }
            }
            //计算三层指标，只找到三层，判定结束
            foreach (var item in thridZBList)
            {
                CalcZBValue(item.ZBZD_ZBBH, zbzdList, zbsjList, submitInfo, cycle, 3);
            }
        }
        #endregion

        #region 校验复合指标...
        public List<KeyValuePair<string, string>> CheckFhZBValue(string submitInfoId, out List<string> errorList)
        {
            errorList = new List<string>();
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoId);

            if (string.IsNullOrEmpty(submitInfo.SubmitInfo_Month)) submitInfo.SubmitInfo_Month = "";
            if (string.IsNullOrEmpty(submitInfo.SubmitInfo_Day)) submitInfo.SubmitInfo_Day = "";
            //此处用new field的原因是month可能 是null或者空值
            IList<T_ZBSJ> zbsjList = DB.From<T_ZBSJ>().Where(T_ZBSJ._.ZBSJ_Cycle == submitInfo.SubmitInfo_Cycle
               && T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID && T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID &&
               T_ZBSJ._.SubmitInfoID == submitInfo.SubmitInfo_ID && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year && new Field("ISNULL(ZBSJ_MONTH,'')") == submitInfo.SubmitInfo_Month
               && new Field("ISNULL(ZBSJ_DAY,'')") == submitInfo.SubmitInfo_Day).ToList();

            IList<T_ZBZD> zbzdList = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == submitInfo.SubmitInfo_ReportID).ToList();
            //校验指标
            IList<T_ZBZD> jyzbzdList = zbzdList.Where(m => !string.IsNullOrEmpty(m.ZBZD_JYFormula)).ToList();

            List<KeyValuePair<string, string>> errZBList = new List<KeyValuePair<string, string>>();

            foreach (var item in jyzbzdList)
            {
                OpType op;
                string compareformula = string.Empty;//存放被比较的语句，可能是1+2这样的语句。
                T_ZBZD rzbzd;//要比较的指标字典
                string[] formulaArray = item.ZBZD_JYFormula.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
               

                decimal lvalue = 0;
                decimal rvalue = 0;
                //先获取原数据，即单元格本身的值。
                T_ZBSJ lzbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == item.ZBZD_ZBBH);
                if (lzbsj == null || string.IsNullOrEmpty(lzbsj.ZBSJ_Value))
                    lvalue = 0;
                else
                {
                    decimal.TryParse(lzbsj.ZBSJ_Value, out lvalue);
                }
                
                
                foreach (var formula in formulaArray)
                {
                    string lsformula=formula.Trim().Replace(" ", "");
                    string czf, czf_name;//将操作符放入变量中，等到错误提示的时候可以用到。
                    
                    //此处判断一定要将>=,<=,放在前在，如果先判断>或<,将不会再判断>=和<=了
                    if (lsformula.StartsWith(">="))
                    {
                        op = OpType.GTorEQ;
                         czf=">=";
                         czf_name = "大于等于";
                         compareformula = lsformula.Replace(">=", "");
                    }
                    else if (lsformula.StartsWith("<="))
                    {
                        op = OpType.LTorEQ;
                        czf = "<=";
                        czf_name = "小于等于";
                        compareformula = lsformula.Replace("<=", "");
                    }
                    else if (lsformula.StartsWith(">"))
                    {
                        op = OpType.GT;
                        czf = ">";
                        czf_name = "大于等于";
                        compareformula = lsformula.Replace(">", "");
                    }
                    else if (lsformula.StartsWith("<"))
                    {
                        op = OpType.LT;
                        czf = "<";
                        czf_name = "小于";
                        compareformula = lsformula.Replace("<", "");
                    }
                    else if (lsformula.StartsWith("="))
                    {
                        op = OpType.EQ;
                        czf = "=";
                        czf_name = "等于";
                        compareformula = lsformula.Replace("=", "");
                    }
                    else
                    {
                        throw new FormatException("指标校验公示格式不正确");
                    }

                    //把要比较的指标替换成对应的值，然后将公式计算后，赋值给rvalue

                    MatchCollection Matches = Regex.Matches(compareformula, @"\[Calc\]\d{1,}");//\[Calc\]中，因为 []都是转义字符，所以要加\

                    //将匹配到的所有指标都替换成值
                    foreach (Match NextMatch in Matches)
                    {
                        decimal tempvalue = 0;//临时存放指标数值

                        //指标数据
                        T_ZBSJ rzbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == NextMatch.Value.Replace("[Calc]", "").Trim());
                        if (rzbsj == null || string.IsNullOrEmpty(rzbsj.ZBSJ_Value))
                            tempvalue = 0;
                        else
                        {
                            decimal.TryParse(rzbsj.ZBSJ_Value, out tempvalue);
                        }

                        compareformula = compareformula.Replace(NextMatch.Value, tempvalue.ToString());

                    }

                    compareformula = compareformula.Replace(" ", "");//替换掉公式中是的空格，如果不替换，万一出现"/ 0"，将报错。
                    //如果公式中出现/0，则直接返回值0，因为除以0将报错
                    if (compareformula.IndexOf("/0") > 0)
                        compareformula = "0";

                  
                    DataTable dt = new DataTable();
                    decimal tempnum = 0;
                    string CalcResault = "";

                    //通过DataTable.Compute来计算字符串中的表达式结果。
                    CalcResault = dt.Compute(compareformula, "false").ToString();

                    //如果计算结果是数值 ，则赋值给rvalue，
                    if (!string.IsNullOrEmpty(CalcResault) && decimal.TryParse(CalcResault, out tempnum))
                        rvalue = tempnum;
                    else
                        rvalue = 0;
                    

                    if (lvalue == 0 && rvalue == 0) continue;
                    string errMsg = string.Empty;

                    ////通过用Eval函数来判断语句是否为真。要用Eval函数，则要添加com引用“Microsoft Script Control 1.0”,并将引用的dll的属性 “嵌入互操作类型”设置为False。

                    //MSScriptControl.ScriptControl sc = new MSScriptControl.ScriptControlClass();
                    //sc.Language = "JavaScript";
                    ////即将 左边值+操作符+右边值 组合成字符串，然后通过Eval进行计算，得到结果
                    //if (sc.Eval(lvalue.ToString() + czf + rvalue.ToString()).ToString().ToUpper() == "FALSE")
                    //{
                    //    errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                    //    errMsg = string.Format("[{0}:{1}]必须{2}[{3}]", item.ZBZD_Index, item.ZBZD_Dimension,czf_name, rvalue.ToString());
                    //    if (!errorList.Contains(errMsg))
                    //    {
                    //        errorList.Add(errMsg);
                    //    }
                    //}


                    switch (op)
                    {
                        case OpType.GT:
                            if (lvalue <= rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须大于[{2}]", item.ZBZD_Index, item.ZBZD_Dimension, rvalue.ToString());
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.EQ:
                            if (lvalue != rvalue)
                            {

                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须等于[{2}]", item.ZBZD_Index, item.ZBZD_Dimension, rvalue.ToString());
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }

                            }
                            break;
                        case OpType.LT:
                            if (lvalue >= rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须小于[{2}]", item.ZBZD_Index, item.ZBZD_Dimension, rvalue.ToString());
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.GTorEQ:
                            if (lvalue < rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须大于或等于[{2}]", item.ZBZD_Index, item.ZBZD_Dimension, rvalue.ToString());
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.LTorEQ:
                            if (lvalue > rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须小于或等于[{2}]", item.ZBZD_Index, item.ZBZD_Dimension, rvalue.ToString());
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                    }
                }
            }
            return errZBList;
        }
        #endregion


        #region 校验指标...
        public List<KeyValuePair<string, string>> CheckZBValue(string submitInfoId, out List<string> errorList)
        {
            errorList = new List<string>();
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoId);

            if (string.IsNullOrEmpty(submitInfo.SubmitInfo_Month)) submitInfo.SubmitInfo_Month = "";
            if (string.IsNullOrEmpty(submitInfo.SubmitInfo_Day)) submitInfo.SubmitInfo_Day = "";
            //此处用new field的原因是month可能 是null或者空值
            IList<T_ZBSJ> zbsjList = DB.From<T_ZBSJ>().Where(T_ZBSJ._.ZBSJ_Cycle == submitInfo.SubmitInfo_Cycle
               && T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID && T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID &&
               T_ZBSJ._.SubmitInfoID == submitInfo.SubmitInfo_ID && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year && new Field("ISNULL(ZBSJ_MONTH,'')") == submitInfo.SubmitInfo_Month
               && new Field("ISNULL(ZBSJ_DAY,'')") == submitInfo.SubmitInfo_Day).ToList();

            IList<T_ZBZD> zbzdList = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == submitInfo.SubmitInfo_ReportID).ToList();
            //校验指标
            IList<T_ZBZD> jyzbzdList = zbzdList.Where(m => !string.IsNullOrEmpty(m.ZBZD_JYFormula)).ToList();

            List<KeyValuePair<string, string>> errZBList = new List<KeyValuePair<string, string>>();

            foreach (var item in jyzbzdList)
            {
                OpType op;
                string compareZBBH = string.Empty;
                string[] formulaArray = item.ZBZD_JYFormula.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var formula in formulaArray)
                {

                    //此处判断一定要将>=,<=,放在前在，如果先判断>或<,将不会再判断>=和<=了
                    if (formula.StartsWith(">="))
                    {
                        op = OpType.GTorEQ;
                        compareZBBH = formula.Replace(">=", "").Replace("[Calc]", "").Trim();
                    }
                    else if (formula.StartsWith("<="))
                    {
                        op = OpType.LTorEQ;
                        compareZBBH = formula.Replace("<=", "").Replace("[Calc]", "").Trim();
                    }
                    else if (formula.StartsWith(">"))
                    {
                        op = OpType.GT;
                        compareZBBH = formula.Replace(">", "").Replace("[Calc]", "").Trim();
                    }
                    else if (formula.StartsWith("<"))
                    {
                        op = OpType.LT;
                        compareZBBH = formula.Replace("<", "").Replace("[Calc]", "").Trim();
                    }
                    else if (formula.StartsWith("="))
                    {
                        op = OpType.EQ;
                        compareZBBH = formula.Replace("=", "").Replace("[Calc]", "").Trim();
                    }
                    else
                    {
                        throw new FormatException("指标校验公示格式不正确");
                    }
                    //要比较的指标字典
                    T_ZBZD rzbzd = zbzdList.FirstOrDefault(m => m.ZBZD_ZBBH == compareZBBH);

                    decimal lvalue = 0;
                    decimal rvalue = 0;
                    //原数据
                    T_ZBSJ lzbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == item.ZBZD_ZBBH);
                    if (lzbsj == null || string.IsNullOrEmpty(lzbsj.ZBSJ_Value))
                        lvalue = 0;
                    else
                    {
                        decimal.TryParse(lzbsj.ZBSJ_Value, out lvalue);
                    }

                    //要比较的指标数据
                    T_ZBSJ rzbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == compareZBBH);
                    if (rzbsj == null || string.IsNullOrEmpty(rzbsj.ZBSJ_Value))
                        rvalue = 0;
                    else
                    {
                        decimal.TryParse(rzbsj.ZBSJ_Value, out rvalue);
                    }

                    if (lvalue == 0 && rvalue == 0) continue;
                    string errMsg = string.Empty;
                    switch (op)
                    {
                        case OpType.GT:
                            if (lvalue <= rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须大于[{2}:{3}]", item.ZBZD_Index, item.ZBZD_Dimension, rzbzd.ZBZD_Index, rzbzd.ZBZD_Dimension);
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.EQ:
                            if (lvalue != rvalue)
                            {

                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须等于[{2}:{3}]", item.ZBZD_Index, item.ZBZD_Dimension, rzbzd.ZBZD_Index, rzbzd.ZBZD_Dimension);
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }

                            }
                            break;
                        case OpType.LT:
                            if (lvalue >= rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须小于[{2}:{3}]", item.ZBZD_Index, item.ZBZD_Dimension, rzbzd.ZBZD_Index, rzbzd.ZBZD_Dimension);
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.GTorEQ:
                            if (lvalue < rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须大于或等于[{2}:{3}]", item.ZBZD_Index, item.ZBZD_Dimension, rzbzd.ZBZD_Index, rzbzd.ZBZD_Dimension);
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                        case OpType.LTorEQ:
                            if (lvalue > rvalue)
                            {
                                errZBList.Add(new KeyValuePair<string, string>(item.ZBZD_Remark.Split(',')[0], item.ZBZD_Remark.Split(',')[1]));
                                errMsg = string.Format("[{0}:{1}]必须小于或等于[{2}:{3}]", item.ZBZD_Index, item.ZBZD_Dimension, rzbzd.ZBZD_Index, rzbzd.ZBZD_Dimension);
                                if (!errorList.Contains(errMsg))
                                {
                                    errorList.Add(errMsg);
                                }
                            }
                            break;
                    }
                }
            }
            return errZBList;
        }
        #endregion

        public string PublishReport(string OrgIds, string ReportIds,
            string Year, string Month, string Jd, int userId, string realName)
        {
            string Msg = string.Empty;
            string sMonth = string.Empty;
            int iCount = 0;
            string[] ArrayOrg = OrgIds.Split(',');
            string[] ArrayReport = ReportIds.Split(',');

            //默认的截止时间
            DateTime defDeadLine = DateTime.Now;

            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    T_Report Report = null;
                    Report = trans.From<T_Report>().Where(T_Report._.Report_ID == ReportIds).ToSingle();

                    switch (Report.Report_Cycle)
                    {
                        case "01"://月报
                            sMonth = Month;
                            iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                                      T_SubmitInfo._.SubmitInfo_Year == Year &&
                                                                      T_SubmitInfo._.SubmitInfo_Month == Month &&
                                                                      T_SubmitInfo._.SubmitInfo_OrganizationID == OrgIds &&
                                                                      T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle);

                            T_DeadlineConfig monthDeadline = trans.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "MONTH");
                            defDeadLine = new DateTime(int.Parse(Year), int.Parse(sMonth), 1).AddDays(monthDeadline.Value);
                            //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                            if ((monthDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                            { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(monthDeadline.FormatDay)); }

                            break;
                        case "02"://季报
                            sMonth = Jd;
                            iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                               T_SubmitInfo._.SubmitInfo_Year == Year &&
                                                               T_SubmitInfo._.SubmitInfo_Month == Jd &&
                                                               T_SubmitInfo._.SubmitInfo_OrganizationID == OrgIds &&
                                                               T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle);

                            T_DeadlineConfig seasonDeadline = trans.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "SEASON");
                            int seasonToMonth = sMonth == "01" ? 1 : sMonth == "02" ? 4 : sMonth == "03" ? 7 : 10;
                            defDeadLine = new DateTime(int.Parse(Year), seasonToMonth, 1).AddDays(seasonDeadline.Value);
                            //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                            if ((seasonDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                            { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(seasonDeadline.FormatDay)); }
                            break;
                        case "03"://年报
                            sMonth = "";
                            iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
                                                               T_SubmitInfo._.SubmitInfo_Year == Year &&
                                                               T_SubmitInfo._.SubmitInfo_OrganizationID == OrgIds &&
                                                               T_SubmitInfo._.SubmitInfo_Cycle == Report.Report_Cycle);

                            T_DeadlineConfig yearDeadline = trans.Single<T_DeadlineConfig>(T_DeadlineConfig._.Key == "YEAR");
                            //农业厅要求年报在次年2月25日前上报，如果是闰年，应该要在配置的默认上报延迟天数上加一天
                            int delayday = yearDeadline.Value;
                            if (int.Parse(Year) % 4 == 0 && int.Parse(Year) % 100 == 0 || int.Parse(Year) % 400 == 0)
                            {
                                delayday += 1;
                            }

                            defDeadLine = new DateTime(int.Parse(Year), 1, 1).AddDays(delayday);
                            //??运算符的作用，如果monthDeadline.FormatDay为空，则使用??后面的值进行运算，等价于monthDeadline.FormatDay==null?0:monthDeadline.FormatDay
                            if ((yearDeadline.FormatDay ?? 0) > 0)//若指定了 必须格式化为固定的日
                            { defDeadLine = new DateTime(defDeadLine.Year, defDeadLine.Month, Convert.ToInt32(yearDeadline.FormatDay)); }
                            break;
                        default:
                            sMonth = "";
                            throw new Exception("没有找到该报表周期下的报表!");
                    }
                    if (iCount == 0)
                    {
                        T_SubmitInfo subinfo = new T_SubmitInfo();
                        subinfo.SubmitInfo_ReportID = Report.Report_ID;
                        subinfo.SubmitInfo_Year = Year;
                        subinfo.SubmitInfo_Month = sMonth; //存季度或月份
                        subinfo.SubmitInfo_Day = "";
                        subinfo.SubmitInfo_OrganizationID = OrgIds; //部门
                        subinfo.SubmitInfo_State = "0"; //未报送
                        subinfo.SubmitInfo_Cycle = Report.Report_Cycle;
                        subinfo.SubmitInfo_CreateDate = DateTime.Now.ToString("yyyy-MM-dd");
                        subinfo.SubmitInfo_EndDate = defDeadLine.ToString("yyyy-MM-dd");
                        subinfo.SubmitInfo_LazyDate = defDeadLine.ToString("yyyy-MM-dd");

                        subinfo.SubmitInfo_Sjly = "手工录入";
                        subinfo.SubmitInfo_CreatorID = userId;
                        subinfo.SubmitInfo_CreatorName = realName;
                        subinfo.Detach();
                        trans.Save<T_SubmitInfo>(subinfo);
                        //提交事务
                        trans.Commit();
                    }
                    else
                    {
                        Msg = "已经存在当期报表!";
                    }

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Msg = ex.Message;
                    throw;
                }
            }
            return Msg;
        }


        //public string PublishReport(string ReportIds, string Year) {
        //    string Msg = string.Empty;
        //    int iCount = 0;
        //    string[] ArrayReport = ReportIds.Split(',');
        //    using (DbTrans trans = DB.BeginTrans()) {
        //        try {
        //            T_SubmitInfo SubmitInfo;
        //            List<T_Organization> OrgList = (List<T_Organization>)trans.From<T_Organization>().ToList();

        //            for (int i = 0; i < ArrayReport.Length; i++) {
        //                //根据报表类型判断 是年报 季报还是月报
        //                T_Report Report = trans.From<T_Report>()
        //                                 .Select(T_Report._.Report_ID, T_Report._.Report_Cycle)
        //                                 .Where(T_Report._.Report_ID == ArrayReport[i])
        //                                 .ToSingle();
        //                switch (Report.Report_Cycle) {
        //                    case "01": //月报
        //                        //从1写到12
        //                        for (int iMonth = 1; iMonth <= 12; iMonth++) {
        //                            foreach (T_Organization item in OrgList) {
        //                                //判断是否存在，如果存在，则跳过
        //                                iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_Year == Year &&
        //                                                                   T_SubmitInfo._.SubmitInfo_Month == iMonth.ToString("00") &&
        //                                                                   T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
        //                                                                   T_SubmitInfo._.SubmitInfo_OrganizationID == item.Organization_ID);
        //                                if (iCount > 0) {
        //                                    throw new Exception("当期报表已经存在，不允许重复操作!");

        //                                };
        //                                SubmitInfo = new T_SubmitInfo();
        //                                SubmitInfo.SubmitInfo_ReportID = Report.Report_ID;
        //                                SubmitInfo.SubmitInfo_Year = Year;
        //                                SubmitInfo.SubmitInfo_Month = iMonth.ToString("00"); //格式化为2位
        //                                SubmitInfo.SubmitInfo_Day = "";
        //                                SubmitInfo.SubmitInfo_OrganizationID = item.Organization_ID.ToString(); //部门
        //                                SubmitInfo.SubmitInfo_State = "0"; //未报送
        //                                SubmitInfo.SubmitInfo_Cycle = Report.Report_Cycle;
        //                                SubmitInfo.SubmitInfo_EndDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
        //                                //
        //                                SubmitInfo.Detach(); //新增
        //                                trans.Save<T_SubmitInfo>(SubmitInfo);
        //                            }
        //                        }
        //                        break;
        //                    case "02"://季报
        //                        for (int iSeason = 1; iSeason <= 4; iSeason++) {
        //                            foreach (T_Organization item in OrgList) {
        //                                //判断是否存在，如果存在，则跳过
        //                                iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_Year == Year &&
        //                                                                   T_SubmitInfo._.SubmitInfo_Month == iSeason.ToString("00") &&
        //                                                                   T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
        //                                                                   T_SubmitInfo._.SubmitInfo_OrganizationID == item.Organization_ID);
        //                                if (iCount > 0) {
        //                                    throw new Exception("当期报表已经存在，不允许重复操作!");
        //                                }
        //                                SubmitInfo = new T_SubmitInfo();
        //                                SubmitInfo.SubmitInfo_ReportID = Report.Report_ID;
        //                                SubmitInfo.SubmitInfo_Year = Year;
        //                                SubmitInfo.SubmitInfo_Month = iSeason.ToString("00"); //格式化为2位
        //                                SubmitInfo.SubmitInfo_Day = "";
        //                                SubmitInfo.SubmitInfo_OrganizationID = item.Organization_ID.ToString(); //部门
        //                                SubmitInfo.SubmitInfo_State = "0"; //未报送
        //                                SubmitInfo.SubmitInfo_Cycle = Report.Report_Cycle;
        //                                SubmitInfo.SubmitInfo_EndDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
        //                                //
        //                                SubmitInfo.Detach(); //新增
        //                                trans.Save<T_SubmitInfo>(SubmitInfo);
        //                            }

        //                        }
        //                        break;
        //                    case "03"://年报
        //                        foreach (T_Organization item in OrgList) {
        //                            iCount = trans.Count<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ReportID == Report.Report_ID &&
        //                                                               T_SubmitInfo._.SubmitInfo_Year == Year &&
        //                                                               T_SubmitInfo._.SubmitInfo_OrganizationID == item.Organization_ID);
        //                            if (iCount > 0) {
        //                                throw new Exception("当期报表已经存在，不允许重复操作!");
        //                            }
        //                            SubmitInfo = new T_SubmitInfo();
        //                            SubmitInfo.SubmitInfo_ReportID = Report.Report_ID;
        //                            SubmitInfo.SubmitInfo_Year = Year;
        //                            SubmitInfo.SubmitInfo_Month = ""; //格式化为2位
        //                            SubmitInfo.SubmitInfo_Day = "";
        //                            SubmitInfo.SubmitInfo_OrganizationID = item.Organization_ID.ToString(); //部门
        //                            SubmitInfo.SubmitInfo_State = "0"; //未报送
        //                            SubmitInfo.SubmitInfo_Cycle = Report.Report_Cycle;
        //                            SubmitInfo.SubmitInfo_EndDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
        //                            //
        //                            SubmitInfo.Detach(); //新增
        //                            trans.Save<T_SubmitInfo>(SubmitInfo);
        //                        }
        //                        break;
        //                    default:
        //                        break;
        //                }
        //            }
        //            //提交事务
        //            trans.Commit();
        //        }
        //        catch (Exception ex) {
        //            trans.Rollback();
        //            Msg = ex.Message;
        //            throw;
        //        }
        //    }
        //    return Msg;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="msg"></param>
        public void Save5000WReport(List<T_5000W> dataList, out string msg)
        {
            msg = string.Empty;
            if (dataList.Count == 0)
            {
                msg = "明细数量不能为空";
                return;
            }
            int submitInfoID = dataList[0].SubmitInfoID.Value;
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            if (submitInfo.SubmitInfo_State == "1")
            {
                msg = "不能修改已上报的数据";
                return;
            }
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    trans.Delete<T_5000W>(T_5000W._.SubmitInfoID == submitInfoID);
                    foreach (var item in dataList)
                    {
                        item.Detach();
                        trans.Save(item);
                    }
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

        public IList<T_Organization> GetChildOrg(string orgid)
        {
            IList<T_Organization> childOrgList = DB.From<T_Organization>().Where(T_Organization._.Organization_ParentID == orgid).ToList();
            return childOrgList;
        }

        public string DeleteSubmitInfo(string id, CurrentUser User, out string Msg)
        {
            Msg = string.Empty;
            using (DbTrans trans = DB.BeginTrans())
            {
                T_SubmitInfo submitInfo = trans.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == id);
                WhereClip where = T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID &&
                    T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID && T_ZBSJ._.SubmitInfoID == id;
                if (submitInfo.SubmitInfo_Cycle == "01" && submitInfo.SubmitInfo_Cycle == "02")
                    where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year && T_ZBSJ._.ZBSJ_MONTH == submitInfo.SubmitInfo_Month;
                else if (submitInfo.SubmitInfo_Cycle == "03") where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year;
                try
                {
                    //T_SubmitInfo obj = trans.From<T_SubmitInfo>().Where(T_SubmitInfo._.SubmitInfo_ID == id).ToSingle();
                    //判断报送状态
                    if (submitInfo.SubmitInfo_State == "1")
                    {
                        throw new Exception("报送状态为已上报，不允许删除!");
                    }
                    //判断两者所属部门是否一致,如果是管理员，可以删除非所在部门的报表
                    if (User.UserInfo.User_OrganizationID.ToString() != submitInfo.SubmitInfo_OrganizationID&&!User.UserInfo.IsAdmin())
                    {
                        throw new Exception("非操作员所在部门，不允许删除!");
                    }
                    trans.Delete<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfo.SubmitInfo_ID);
                    #region //判断是否为特殊报表(农业化产业龙头企业季报) 删除时需特殊处理 add 2013-07-23
                    if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYJBReportID"] == submitInfo.SubmitInfo_ReportID)
                    {
                        trans.Delete<T_NYCYHLTQYJB>(T_NYCYHLTQYJB._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                    }
                    #endregion

                    #region //判断是否为特殊报表(农业化产业龙头企业年报) 删除时需特殊处理 add 2013-07-23
                    if (System.Configuration.ConfigurationManager.AppSettings["NYCYHLTQYNBReportID"] == submitInfo.SubmitInfo_ReportID)
                    {
                        trans.Delete<T_NYCYHLTQYNB1>(T_NYCYHLTQYNB1._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB2>(T_NYCYHLTQYNB2._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB3>(T_NYCYHLTQYNB3._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB4>(T_NYCYHLTQYNB4._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB5>(T_NYCYHLTQYNB5._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB6>(T_NYCYHLTQYNB6._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                        trans.Delete<T_NYCYHLTQYNB7>(T_NYCYHLTQYNB7._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                    }
                    #endregion

                    //删除指标数据
                    trans.Delete<T_ZBSJ>(where);

                    #region //判断是否为河南省规模以上休闲农业企业主要经济指标季报表a 该表需单独删除
                    if (System.Configuration.ConfigurationManager.AppSettings["XXNYQYJBReportID"] == submitInfo.SubmitInfo_ReportID)
                    {
                        trans.Delete<T_XXNYQYJB>(T_XXNYQYJB._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                    }
                    #endregion

                    #region  //判断是否为5000W的表 该表需单独处理
                    if (System.Configuration.ConfigurationManager.AppSettings["NSYWQWReportID"] == submitInfo.SubmitInfo_ReportID)
                    {
                        trans.Delete<T_5000W>(T_5000W._.SubmitInfoID == submitInfo.SubmitInfo_ID);
                    }
                    #endregion


                    //提交事务
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Msg = ex.Message;
                    throw;
                }
            }
            return Msg;
        }

        public DataTable Get5000WReportDT(WhereClip where, QueryBase query)
        {
            Field[] fields = { T_5000W._.All };
            var section = DB.From<T_5000W>().Where(where).Select(fields);
            return section.ToTablePage(query);
        }




        public MemoryStream Get5000WReportStream(int submitInfoID)
        {

            Field[] fields = {T_5000W._.CompanyName,T_5000W._.Districts,T_5000W._.Trade,T_5000W._.Takings
                             ,T_5000W._.PaymentTaxation,T_5000W._.TotalProfit,T_5000W._.WorkerPay,T_5000W._.TotalAssets,
                             T_5000W._.FixedAssets,T_5000W._.IsHeader,T_5000W._.Corporate,T_5000W._.LinkPhone};
            DataTable dt = DB.From<T_5000W>().Where(T_5000W._.SubmitInfoID == submitInfoID).Select(fields).ToDataTable();
            HSSFWorkbook book = null;
            //string templetePath = AppDomain.CurrentDomain.BaseDirectory + "5000WTemplete.xls";

            //using (FileStream fs = new FileStream(templetePath, FileMode.Open))
            //{
            //    book = new HSSFWorkbook(fs);
            //}

            T_SubmitInfo submitinfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitinfo.SubmitInfo_ReportID);
            using (MemoryStream templeteStream = new MemoryStream())
            {
                templeteStream.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                book = new NPOI.HSSF.UserModel.HSSFWorkbook(templeteStream);
            }

            var sheet = book.GetSheetAt(0);
            var row = sheet.GetRow(3);
            if (dt.Rows.Count - 1 > 0)//如果总记录数大于1，则要新增加行，因为模版中已经有一空行。所以当记录只有一条时，不用新增。
                sheet.InsertRow(4, dt.Rows.Count - 1, row);
            int startIndex = 3;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.GetRow(startIndex);
                row.Cells[0].SetCellValue(i + 1);
                if (dt.Rows[i]["CompanyName"] != DBNull.Value)
                {
                    row.Cells[1].SetCellValue((string)dt.Rows[i]["CompanyName"]);
                }
                if (dt.Rows[i]["Districts"] != DBNull.Value)
                {
                    row.Cells[2].SetCellValue((string)dt.Rows[i]["Districts"]);
                }
                if (dt.Rows[i]["Trade"] != DBNull.Value)
                {
                    row.Cells[3].SetCellValue((string)dt.Rows[i]["Trade"]);
                }
                if (dt.Rows[i]["Takings"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["Takings"]) != 0)
                    {
                        row.Cells[4].SetCellValue(Convert.ToDouble(dt.Rows[i]["Takings"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["PaymentTaxation"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["PaymentTaxation"]) != 0)
                    {
                        row.Cells[5].SetCellValue(Convert.ToDouble(dt.Rows[i]["PaymentTaxation"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["TotalProfit"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["TotalProfit"]) != 0)
                    {
                        row.Cells[6].SetCellValue(Convert.ToDouble(dt.Rows[i]["TotalProfit"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["WorkerPay"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["WorkerPay"]) != 0)
                    {
                        row.Cells[7].SetCellValue(Convert.ToDouble(dt.Rows[i]["WorkerPay"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["TotalAssets"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["TotalAssets"]) != 0)
                    {
                        row.Cells[8].SetCellValue(Convert.ToDouble(dt.Rows[i]["TotalAssets"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["FixedAssets"] != DBNull.Value)
                {
                    if (Convert.ToDouble(dt.Rows[i]["FixedAssets"]) != 0)
                    {
                        row.Cells[9].SetCellValue(Convert.ToDouble(dt.Rows[i]["FixedAssets"]).ToString("0.00"));
                    }
                }
                if (dt.Rows[i]["IsHeader"] != DBNull.Value)
                {
                    row.Cells[10].SetCellValue(Convert.ToInt32(dt.Rows[i]["IsHeader"]) == 1 ? "是" : "否");
                }
                if (dt.Rows[i]["Corporate"] != DBNull.Value)
                {
                    row.Cells[11].SetCellValue((string)dt.Rows[i]["Corporate"]);
                }
                if (dt.Rows[i]["LinkPhone"] != DBNull.Value)
                {
                    row.Cells[12].SetCellValue((string)dt.Rows[i]["LinkPhone"]);
                }
                startIndex++;
            }


            //处理 单位和期间
            T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);

            string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                //if (b1 && b2 && b3 && b4 && b5) break;
                IRow irow = sheet.GetRow(i);
                for (int j = 0; j < irow.PhysicalNumberOfCells; j++)
                {
                    //if (b1 && b2 && b3 && b4 && b5) break;
                    if (irow.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
                    switch (irow.Cells[j].StringCellValue)
                    {
                        case "{ZHDW}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("综合单位:" + organize.Organization_Name);
                                    //b1 = true;
                                }
                                break;
                            }
                        case "{QJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                //b2 = true;
                                break;
                            }
                        case "{DWFZR}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
                                    //b3 = true;

                                }
                                break;
                            }
                        case "{ZBR}":
                            {
                                irow.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
                                //b4 = true;
                                break;
                            }
                        case "{BCRQ}":
                            {
                                irow.Cells[j].SetCellValue("报出日期：" + sbrq);
                                //b5 = true;
                                break;
                            }
                    }
                }
            }


            MemoryStream stream = new MemoryStream();
            book.Write(stream);
            return stream;
        }

        public void SetLazyDate(CurrentUser CurrUser, int submitInfoID, DateTime dtLazyDate, out string msg)
        {
            msg = string.Empty;
            T_SubmitInfo submintInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            T_Organization organization = GetEntity<T_Organization>(T_Organization._.Organization_ID == submintInfo.SubmitInfo_OrganizationID);
            if (dtLazyDate < DateTime.Parse(submintInfo.SubmitInfo_LazyDate))
            {
                msg = "延迟时间不能小于初始截止时间";
                return;
            }
            if (organization.Organization_ParentID != CurrUser.UserInfo.User_OrganizationID && !CurrUser.UserInfo.IsAdmin())
            {
                msg = "不能对非直接下级部门的报表设置延迟上报时间";
                return;
            }
            if (!string.IsNullOrEmpty(submintInfo.SubmitInfo_LazyDate) && submintInfo.SubmitInfo_LazyDate != submintInfo.SubmitInfo_EndDate && !CurrUser.UserInfo.IsAdmin())
            {
                msg = "该报表已经设置过延迟时间，不能再次设置";
                return;
            }
            if (submintInfo.SubmitInfo_State != "0")
            {
                msg = "该报表不是未上报状态，不能设置延迟上报时间";
                return;
            }
            submintInfo.SubmitInfo_LazyDate = dtLazyDate.ToString("yyyy-MM-dd");
            submintInfo.Attach();
            DB.Save(submintInfo);
        }


        public System.IO.MemoryStream GetExcelStream(string SubmitInfoId, CurrentUser CurrUser, out string msg)
        {//, string year, string month, string cycle, string dw
            msg = string.Empty;
            T_SubmitInfo submitinfo = GetEntity<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == SubmitInfoId);

            IList<T_ZBSJ> zbsjList = DB.From<T_ZBSJ>().Where(T_ZBSJ._.ZBSJ_Cycle == submitinfo.SubmitInfo_Cycle
                && T_ZBSJ._.ZBSJ_ReportID == submitinfo.SubmitInfo_ReportID && T_ZBSJ._.SubmitInfoID == submitinfo.SubmitInfo_ID &&
                T_ZBSJ._.ZBSJ_DWID == submitinfo.SubmitInfo_OrganizationID && T_ZBSJ._.ZBSJ_YEAR == submitinfo.SubmitInfo_Year && T_ZBSJ._.ZBSJ_MONTH == submitinfo.SubmitInfo_Month).ToList();

            IList<T_ZBZD> zbzdList = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == submitinfo.SubmitInfo_ReportID).ToList();

            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitinfo.SubmitInfo_ReportID);
            T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);

            string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            if (report.Report_ExportTemplete == null || report.Report_ExportTemplete.Length == 0)
            {
                msg = "没有设置导出模版";
                return null;
            }
            using (MemoryStream templeteStream = new MemoryStream())
            {
                templeteStream.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                book = new NPOI.HSSF.UserModel.HSSFWorkbook(templeteStream);
            }

            //using (System.IO.FileStream fs = new System.IO.FileStream("E:\\Book1.xls", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite)) {
            //    book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            //}
            var sheet = book.GetSheetAt(0);
            //bool b1 = false, b2 = false, b3 = false, b4 = false, b5 = false;//五个标志用来指示，五个要填加在表头的项目，如果都为true，则说明全部都添加上了，跳出循环
            //处理 单位和期间
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                //if (b1 && b2 && b3 && b4 && b5) break;
                var row = sheet.GetRow(i);
                for (int j = 0; j < row.PhysicalNumberOfCells; j++)
                {
                    //if (b1 && b2 && b3 && b4 && b5) break;
                    if (row.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
                    switch (row.Cells[j].StringCellValue)
                    {
                        case "{ZHDW}":
                            {
                                if (organize != null)
                                {
                                    row.Cells[j].SetCellValue("综合单位:" + organize.Organization_Name);
                                    //b1 = true;
                                }
                                break;
                            }

                        case "{TBDW}":
                            {
                                if (organize != null)
                                {
                                    row.Cells[j].SetCellValue("制表单位:" + organize.Organization_Name);
                                }
                                break;
                            }
                        case "{DWMC}":
                            {
                                if (organize != null)
                                {
                                    row.Cells[j].SetCellValue("单位名称:" + organize.Organization_Name);
                                }
                                break;
                            }
                        case "{FRDWMC}":
                            {
                                if (organize != null)
                                {
                                    row.Cells[j].SetCellValue(organize.Organization_Name);
                                }
                                break;
                            }
                        case "{QJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        row.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        row.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        row.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                //b2 = true;
                                break;
                            }
                        case "{TBQJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        row.Cells[j].SetCellValue(string.Format("填报时间：{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        row.Cells[j].SetCellValue(string.Format("填报时间：{0}年 第{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        row.Cells[j].SetCellValue(string.Format("填报时间：{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                //b2 = true;
                                break;
                            }
                        case "{DWFZR}":
                            {
                                if (organize != null)
                                {
                                    row.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
                                    //b3 = true;

                                }
                                break;
                            }
                        case "{ZBR}":
                            {
                                row.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
                                //b4 = true;
                                break;
                            }

                        case "{TBR}":
                            {
                                row.Cells[j].SetCellValue("填表人：" + submitinfo.SubmitInfo_CreatorName);
                                break;
                            }
                        case "{BCRQ}":
                            {
                                row.Cells[j].SetCellValue("报出日期：" + sbrq);
                                //b5 = true;
                                break;
                            }
                        case "{TBRQ}":
                            {
                                row.Cells[j].SetCellValue("填报日期：" + sbrq);
                                //b5 = true;
                                break;
                            }
                    }

                    //将[calc00000]指标替换在真实数据
                    if (row.Cells[j].StringCellValue.ToString().Trim().ToUpper().IndexOf("[CALC]") >= 0)
                    {
                        string Value = row.Cells[j].StringCellValue.ToString().Trim().ToUpper();
                        string zbzd_zbbh = Value.Substring(Value.IndexOf("[CALC]") + "[CALC]".Length);
                        T_ZBSJ zbsj_value = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == zbzd_zbbh);
                        if (zbsj_value == null)
                        {
                            row.Cells[j].SetCellValue("");
                            continue;
                        }

                        //处理0不显示
                        double cellValue = 0d;
                        if (double.TryParse(zbsj_value.ZBSJ_Value, out cellValue))
                        {
                            if (cellValue == 0)
                            {
                                row.Cells[j].SetCellValue("");
                            }
                            else
                                row.Cells[j].SetCellValue(zbsj_value.ZBSJ_Value);

                        }
                        else
                        {
                            row.Cells[j].SetCellValue(zbsj_value.ZBSJ_Value);
                        }

                    }


                }
            }
            //处理单位和期间//已加上
            //原来的数值填充方法
            //T_ZBSJ zbsj = null;
            //foreach (var zbzd in zbzdList) {
            //    zbsj = zbsjList.FirstOrDefault(m => m.ZBSJ_ZBBH == zbzd.ZBZD_ZBBH);
            //    if (zbsj == null) continue;
            //    if (string.IsNullOrEmpty(zbsj.ZBSJ_Value)) continue;
            //    int rowIndex = int.Parse(zbzd.ZBZD_Remark.Split(',')[0]);
            //    int colIndex = int.Parse(zbzd.ZBZD_Remark.Split(',')[1]);
            //    var row = sheet.GetRow(rowIndex - 1);
            //    double cellValue = 0d;
            //    if (double.TryParse(zbsj.ZBSJ_Value, out cellValue)) {
            //        if (cellValue == 0) {
            //            row.Cells[colIndex - 1].SetCellValue("");
            //        }
            //        else
            //            row.Cells[colIndex - 1].SetCellValue(cellValue);
            //    }
            //    else {
            //        row.Cells[colIndex - 1].SetCellValue(zbsj.ZBSJ_Value);
            //    }
            //}
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Write(stream);
            return stream;
        }

        public string importReport(MemoryStream stream, string submitInfoId)
        {

            string msg = string.Empty;
            double value;
            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            using (stream)
            {
                book = new HSSFWorkbook(stream);
            }
            var sheet = book.GetSheetAt(0);
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    T_SubmitInfo submitInfo = trans.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoId);
                    IList<T_ZBZD> zbzdList = trans.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == submitInfo.SubmitInfo_ReportID).ToList();
                    IList<T_ReportContents> reportcontentslist = trans.From<T_ReportContents>().Where(T_ReportContents._.CONTENTS_REPORTID == submitInfo.SubmitInfo_ReportID).ToList();

                    //删除已存在的指标数据
                    WhereClip where = T_ZBSJ._.ZBSJ_ReportID == submitInfo.SubmitInfo_ReportID &&
                        T_ZBSJ._.ZBSJ_DWID == submitInfo.SubmitInfo_OrganizationID && T_ZBSJ._.SubmitInfoID == submitInfoId;

                    if (submitInfo.SubmitInfo_Cycle == "01" || submitInfo.SubmitInfo_Cycle == "02")
                        where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year &&
                            T_ZBSJ._.ZBSJ_MONTH == submitInfo.SubmitInfo_Month;
                    else if (submitInfo.SubmitInfo_Cycle == "03")
                        where = where && T_ZBSJ._.ZBSJ_YEAR == submitInfo.SubmitInfo_Year;

                    trans.Delete<T_ZBSJ>(where);


                    foreach (var item in zbzdList)
                    {
                        string[] array = item.ZBZD_Remark.Split(',');
                        int rownum = int.Parse(array[0]) - 1, cellnum = int.Parse(array[1]) - 1;

                        //如果指标所在的行列大于文档的行列，则跳出本次循环，减1的原因是指标的开始行号列号为1，但excel文档中的开始行列号为0
                        if (rownum > sheet.LastRowNum)
                            continue;
                        var row = sheet.GetRow(rownum);

                        if (cellnum >= row.PhysicalNumberOfCells)
                            continue;
                        var cell = row.GetCell(cellnum);
                        T_ReportContents reportcontents = reportcontentslist.FirstOrDefault(m => (m.CONTENTS_ROW == int.Parse(array[0]) && m.CONTENTS_COL == int.Parse(array[1])));

                        T_ZBSJ zbsj = new T_ZBSJ();
                        zbsj.SubmitInfoID = submitInfo.SubmitInfo_ID;
                        zbsj.ZBSJ_ReportID = submitInfo.SubmitInfo_ReportID;
                        zbsj.ZBSJ_YEAR = submitInfo.SubmitInfo_Year;
                        zbsj.ZBSJ_MONTH = submitInfo.SubmitInfo_Month;
                        zbsj.ZBSJ_DWID = submitInfo.SubmitInfo_OrganizationID;
                        zbsj.ZBSJ_Cycle = submitInfo.SubmitInfo_Cycle;
                        zbsj.ZBSJ_ZBBH = item.ZBZD_ZBBH;

                        string cellValue = string.Empty;

                        if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
                            cellValue = cell.StringCellValue;
                        else if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                            cellValue = cell.NumericCellValue.ToString();
                        zbsj.ZBSJ_Value = cellValue;

                        //格式化zbsj的小数点
                        if (!string.IsNullOrEmpty(reportcontents.CONTENTS_Format))
                        { //如果开头值为1，说明是数值型，要处理小数位数
                            if (reportcontents.CONTENTS_Format.StartsWith("1"))
                            {
                                //如果是空值，跳出本次循环
                                if (string.IsNullOrEmpty(cellValue))
                                    continue;
                                int digits = int.Parse(reportcontents.CONTENTS_Format.Split(',')[1]);
                                //设置格式化小数点的格式，最终拼成“0.00”这样的字符串。
                                string formatdigits = "0.";
                                for (int i = 0; i < digits; i++)
                                    formatdigits += "0";

                                if (double.TryParse(cellValue, out value) == false)
                                {
                                    throw new Exception("纬度【" + item.ZBZD_Index + "】，指标【" + item.ZBZD_Dimension + "】要求为数字类型,但输入的值为【" + cellValue + "】！");
                                }
                                else
                                {
                                    zbsj.ZBSJ_Value = value.ToString(formatdigits);

                                    //如果是零值，跳出本次循环
                                    if (double.TryParse(zbsj.ZBSJ_Value, out value))
                                    {
                                        if (value == 0)
                                            continue;
                                    }
                                }
                            }

                        }
                        if (zbsj.ZBSJ_Value == null)
                            continue;


                        zbsj.Detach();
                        trans.Save(zbsj);
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    msg = ex.Message;
                    throw;
                }
            }
            return msg;
        }

        public static List<T> JSONStringToList<T>(string JsonStr)
        {

            JsonStr = Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(JsonStr));
            JavaScriptSerializer Serializer = new JavaScriptSerializer();
            List<T> objs = Serializer.Deserialize<List<T>>(JsonStr);
            return objs;
        }


        //用于非模版类报表的导入
        public DataTable importReportExA(MemoryStream stream, string submitInfoId, string colModels)
        {

            string msg = string.Empty;
            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            using (stream)
            { //流必须要释放
                book = new HSSFWorkbook(stream);
            }
            var sheet1 = book.GetSheetAt(0);
            DataTable table = new DataTable();//

            //获取字段名称和真实名称
            List<colModel> objs = JSONStringToList<colModel>(colModels);
            if (sheet1.LastRowNum >= 1001) { throw (new Exception("最多只能导入1000行数据，导入失败！")); }


            var row1 = sheet1.GetRow(0);//获取第一行即标头
            //把第一行的数据添加到datatable的列名
            for (int i = 0; i < row1.PhysicalNumberOfCells; i++)
            {
                string cellvalue = row1.GetCell(i).StringCellValue.Replace("\r\n", "").Replace("\r", "").Replace("\n", "").Trim();
                colModel aa = objs.FirstOrDefault(m => m.label == cellvalue);
                //if (aa == null) continue;//如果列不在要导入的字段范围内，跳过
                DataColumn column = new DataColumn();
                if (aa != null)
                {
                    column.ColumnName = aa.name;
                    column.Caption = cellvalue;
                }
                else
                {//如果该列是jqgrid不需要的列,则将Caption置空，最后删除该列。
                    column.ColumnName = cellvalue;
                    column.Caption = "";
                }
                table.Columns.Add(column);
            }
            //插入上报报表id列
            DataColumn column1 = new DataColumn();
            column1.Caption = "SubmitInfoID";
            column1.ColumnName = "SubmitInfoID";
            table.Columns.Add(column1);
            //todo将数据插入到table中。

            int rowCount = sheet1.LastRowNum; //总行数

            //把每行数据添加到datatable中
            for (int i = (sheet1.FirstRowNum + 1); i <= sheet1.LastRowNum; i++)
            {
                IRow row = sheet1.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {

                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dataRow[table.Columns.Count - 1] = submitInfoId;
                table.Rows.Add(dataRow);
            }
            //将column1.Caption为空或为null的列清除，因为这些列在jqgrid中没有。
            for (int i = table.Columns.Count - 1; i >= 0; i--)
            {
                DataColumn columntemp = table.Columns[i];
                if (string.IsNullOrEmpty(columntemp.Caption.Trim()))
                {
                    table.Columns.RemoveAt(i);
                }
            }

            book = null; //清空工作簿--释放资源
            sheet1 = null;  //清空sheet

            return table;
        }

        private void FillLTQNBTable(DataSet ds, HSSFWorkbook book, string colModelString, string submitInfoId,
            int sheetIndex, int dataStartNum
            )
        {

            List<colModel> colModels = JSONStringToList<colModel>(colModelString);
            var sheet = book.GetSheetAt(sheetIndex);
            DataTable dt = new DataTable();
            foreach (colModel aa in colModels)
            {
                DataColumn column = new DataColumn();
                if (aa.name != "SubmitInfoID" && aa.name != "rn" && aa.name != "cb" && aa.name != "ID")
                {
                    column.ColumnName = aa.name;
                    column.Caption = aa.label;
                    dt.Columns.Add(column);
                }
            }

            DataColumn column1 = new DataColumn();
            column1.Caption = "SubmitInfoID";
            column1.ColumnName = "SubmitInfoID";
            dt.Columns.Add(column1);

            int rowCount = sheet.LastRowNum;
            for (int i = (sheet.FirstRowNum + dataStartNum); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                //如果某行的第一列为空，即企业编号列，则直接跳过该行，这样可以在前台判断各表有数据的行数是否一致。
                if (row == null || row.FirstCellNum < 0 || string.IsNullOrEmpty(row.GetCell(row.FirstCellNum).ToString().Trim())) continue;

                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {

                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dataRow[dt.Columns.Count - 1] = submitInfoId;
                dt.Rows.Add(dataRow);
            }
            //将column1.Caption为空或为null的列清除，因为这些列在jqgrid中没有。
            for (int i = dt.Columns.Count - 1; i >= 0; i--)
            {
                DataColumn columntemp = dt.Columns[i];
                if (string.IsNullOrEmpty(columntemp.Caption.Trim()))
                {
                    dt.Columns.RemoveAt(i);
                }
            }
            //将dt按企业编号排下序
            dt.DefaultView.Sort = "QYBH ASC";
            dt = dt.DefaultView.ToTable();
            ds.Tables.Add(dt);
        }


        //龙头企业年报的导入
        public DataSet ImportLTQYNB(MemoryStream stream, string submitInfoId,
            string colModel1,
            string colModel2,
            string colModel3,
            string colModel4,
            string colModel5,
            string colModel6,
            string colModel7,
            out string msg)
        {
            msg = string.Empty;
            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            using (stream)
            { //流必须要释放
                book = new HSSFWorkbook(stream);
            }
            if (book.NumberOfSheets < 7)
            {
                msg = "导入数据的格式不正确，请确保所有的表都在导入文件内";
                return null;
            }
            try
            {
                DataSet ds = new DataSet();
                System.Web.Script.Serialization.JavaScriptSerializer jss = new JavaScriptSerializer();

                FillLTQNBTable(ds, book, colModel1, submitInfoId, 0, 5);
                FillLTQNBTable(ds, book, colModel2, submitInfoId, 1, 5);
                FillLTQNBTable(ds, book, colModel3, submitInfoId, 2, 5);
                FillLTQNBTable(ds, book, colModel4, submitInfoId, 3, 5);
                FillLTQNBTable(ds, book, colModel5, submitInfoId, 4, 5);
                FillLTQNBTable(ds, book, colModel6, submitInfoId, 5, 5);
                FillLTQNBTable(ds, book, colModel7, submitInfoId, 6, 5);
                return ds;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return null;
            }
        }

        //河南农业产业化龙头企业季报表导入，因为表头比较特殊，显示字段名称相同，用通用的方法无法处理
        public DataTable importReportExB(MemoryStream stream, string submitInfoId, string colModels)
        {
            string msg = string.Empty;

            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            using (stream)
            { //流必须要释放
                book = new HSSFWorkbook(stream);
            }
            var sheet1 = book.GetSheetAt(0);
            DataTable table = new DataTable();//

            //获取字段名称和真实名称
            List<colModel> objs = JSONStringToList<colModel>(colModels);
            if (sheet1.LastRowNum >= 50) { throw (new Exception("最多只能导入50行数据，导入失败！")); }


            //将传过来的colModels递归，找出列名
            foreach (colModel aa in objs)
            {
                DataColumn column = new DataColumn();
                //判断不为SubmitInfoID原因是，前台传过来的列名已经有了SubmitInfoID，但是后台处理时，需要将
                //  SubmitInfoID放在最后一列，用以插入当前的 SubmitInfoID的值,r所以该列单独添加
                if (aa.name != "SubmitInfoID" && aa.name != "rn" && aa.name != "cb")
                {
                    column.ColumnName = aa.name;
                    column.Caption = aa.label;
                    table.Columns.Add(column);
                }
            }
            //插入上报报表id列
            DataColumn column1 = new DataColumn();
            column1.Caption = "SubmitInfoID";
            column1.ColumnName = "SubmitInfoID";
            table.Columns.Add(column1);
            //todo将数据插入到table中。

            int rowCount = sheet1.LastRowNum; //总行数

            //把每行数据添加到datatable中,因为有合并表头，所以从第二行开始，才是有效数据
            for (int i = (sheet1.FirstRowNum + 3); i <= sheet1.LastRowNum; i++)
            {
                IRow row = sheet1.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {

                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dataRow[table.Columns.Count - 1] = submitInfoId;
                table.Rows.Add(dataRow);
            }
            //将column1.Caption为空或为null的列清除，因为这些列在jqgrid中没有。
            for (int i = table.Columns.Count - 1; i >= 0; i--)
            {
                DataColumn columntemp = table.Columns[i];
                if (string.IsNullOrEmpty(columntemp.Caption.Trim()))
                {
                    table.Columns.RemoveAt(i);
                }
            }

            book = null; //清空工作簿--释放资源
            sheet1 = null;  //清空sheet

            return table;
        }

        public DataTable GetXXNYQYJBDT(WhereClip where, QueryBase query)
        {
            Field[] fields = { T_XXNYQYJB._.All };
            var section = DB.From<T_XXNYQYJB>().Where(where).Select(fields);
            return section.ToTablePage(query);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="msg"></param>
        public void SaveXXNYQYJB(List<T_XXNYQYJB> dataList, out string msg)
        {
            msg = string.Empty;
            if (dataList.Count == 0)
            {
                msg = "明细数量不能为空";
                return;
            }
            int submitInfoID = dataList[0].SubmitInfoID.Value;
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            if (submitInfo.SubmitInfo_State == "1")
            {
                msg = "不能修改已上报的数据";
                return;
            }
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    trans.Delete<T_XXNYQYJB>(T_XXNYQYJB._.SubmitInfoID == submitInfoID);
                    foreach (var item in dataList)
                    {
                        item.Detach();
                        trans.Save(item);
                    }
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

        private void WriteWookBook(HSSFWorkbook book, T_SubmitInfo submitinfo, int tableInex, int startInsertIndex, string Organization_Level)
        {
            string tableName = "T_NYCYHLTQYNB" + tableInex;
            WhereClip where=new WhereClip(tableName+".QYBH=T_Organization.Organization_ID");
            QueryCreator qc = QueryCreator.NewCreator().From(tableName)
                .Join<T_Organization>(JoinType.LeftJoin, where)
                .AddWhere(tableName + "].[SubmitInfoID", submitinfo.SubmitInfo_ID)
                ;

            if (!string.IsNullOrEmpty(Organization_Level))
                qc = qc.AddWhere( T_Organization._.Organization_Level == Organization_Level);
            if (tableInex == 1)//如果是第一个表，加一列 龙头企业级别
            {
                 Field[] fields = {T_NYCYHLTQYNB1._.All, new Field("CASE ORGANIZATION_LEVEL WHEN '1' THEN '国家级' WHEN '2' THEN '省级' WHEN '3' THEN '市级' ELSE '' END AS 'LTQYDJ'")
                                  };
                foreach(var field in fields)
                 qc = qc.AddField(field);
             }
               
            DataTable dt = DB.From(qc).ToTable() as DataTable;
            var sheet = book.GetSheetAt(tableInex - 1);
            var row = sheet.GetRow(startInsertIndex);
            if (dt.Rows.Count - 1 > 0)//如果总记录数大于1，则要新增加行，因为模版中已经有一空行。所以当记录只有一条时，不用新增。
                sheet.InsertRow(startInsertIndex + 1, dt.Rows.Count - 1, row);
            int startIndex = startInsertIndex;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.GetRow(startIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j > 1)
                    { //从第三列开始
                        decimal lvalue = 0;
                        if (decimal.TryParse(dt.Rows[i][j].ToString(), out lvalue))
                        {
                            if (lvalue == 0)
                                row.Cells[j - 2].SetCellValue("");
                            else
                                row.Cells[j - 2].SetCellValue(Convert.ToString(dt.Rows[i][j]));
                        }
                        else
                            row.Cells[j - 2].SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                startIndex++;
            }
            //处理 单位和期间
            T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);
            string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                IRow irow = sheet.GetRow(i);
                for (int j = 0; j < irow.PhysicalNumberOfCells; j++)
                {
                    if (irow.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
                    switch (irow.Cells[j].StringCellValue)
                    {
                        case "{ZHDW}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("综合单位:" + organize.Organization_Name);
                                }
                                break;
                            }
                        case "{TBDW}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("制表单位:" + organize.Organization_Name);
                                }
                                break;
                            }
                        case"{DWMC}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("单位名称:" + organize.Organization_Name);
                                }
                                break;
                            }
                        case "{QJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                break;
                            }
                        case "{DWFZR}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
                                }
                                break;
                            }
                        case "{ZBR}":
                            {
                                irow.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
                                break;
                            }
                        case "{TBR}":
                            {
                                irow.Cells[j].SetCellValue("填表人：" + submitinfo.SubmitInfo_CreatorName);
                                break;
                            }
                        case "{BCRQ}":
                            {
                                irow.Cells[j].SetCellValue("报出日期：" + sbrq);
                                break;
                            }
                    }
                }
            }
        }


        /// <summary>
        /// 龙头企业年报导出
        /// </summary>
        /// <param name="submitInfoID"></param>
        /// <returns></returns>
        public MemoryStream GetNYCYHLTQYNBStream(int submitInfoID, string Organization_Level)
        {
            T_SubmitInfo submitinfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitinfo.SubmitInfo_ReportID);
            HSSFWorkbook book = null;
            using (MemoryStream templeteStream = new MemoryStream())
            {
                templeteStream.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                book = new NPOI.HSSF.UserModel.HSSFWorkbook(templeteStream);
            }
            WriteWookBook(book, submitinfo, 1, 6, Organization_Level);//表一
            WriteWookBook(book, submitinfo, 2, 6, Organization_Level);//表二
            WriteWookBook(book, submitinfo, 3, 6, Organization_Level);//表三
            WriteWookBook(book, submitinfo, 4, 6, Organization_Level);//表四
            WriteWookBook(book, submitinfo, 5, 6, Organization_Level);//表五
            WriteWookBook(book, submitinfo, 6, 6, Organization_Level);//表六
            WriteWookBook(book, submitinfo, 7, 6, Organization_Level);//表七
            #region MyRegion
            ////处理 单位和期间
            //T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);
            //string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            //for (int i = 0; i <= sheet.LastRowNum; i++) {
            //    IRow irow = sheet.GetRow(i);
            //    for (int j = 0; j < irow.PhysicalNumberOfCells; j++) {
            //        if (irow.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
            //        switch (irow.Cells[j].StringCellValue) {
            //            case "{ZHDW}": {
            //                    if (organize != null) {
            //                        irow.Cells[j].SetCellValue("综合单位:" + organize.Organization_Name);
            //                    }
            //                    break;
            //                }
            //            case "{QJ}": {
            //                    switch (submitinfo.SubmitInfo_Cycle) {
            //                        case "01":
            //                            irow.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
            //                            break;
            //                        case "02":
            //                            irow.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
            //                            break;
            //                        case "03":
            //                            irow.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
            //                            break;
            //                    }
            //                    break;
            //                }
            //            case "{DWFZR}": {
            //                    if (organize != null) {
            //                        irow.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
            //                    }
            //                    break;
            //                }
            //            case "{ZBR}": {
            //                    irow.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
            //                    break;
            //                }
            //            case "{BCRQ}": {
            //                    irow.Cells[j].SetCellValue("报出日期：" + sbrq);
            //                    break;
            //                }
            //        }
            //    }
            //}
            #endregion
            MemoryStream stream = new MemoryStream();
            book.Write(stream);
            return stream;
        }

        public MemoryStream GetXXNYQYJBStream(int submitInfoID)
        {

            Field[] fields = {
                                 T_XXNYQYJB._.CompanyName
                                ,T_XXNYQYJB._.CYRY
                                ,T_XXNYQYJB._.JDYKRS
                                ,T_XXNYQYJB._.KFPJRZL
                                ,T_XXNYQYJB._.JYZSR
                                ,T_XXNYQYJB._.NCPJYSR
                                ,T_XXNYQYJB._.SGXL
                                ,T_XXNYQYJB._.SCXL
                                ,T_XXNYQYJB._.CQXL
                                ,T_XXNYQYJB._.QDXL
                                ,T_XXNYQYJB._.SCPXL
                                ,T_XXNYQYJB._.JDMJ
                             };
            DataTable dt = DB.From<T_XXNYQYJB>().Where(T_XXNYQYJB._.SubmitInfoID == submitInfoID).Select(fields).ToDataTable();
            HSSFWorkbook book = null;

            T_SubmitInfo submitinfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitinfo.SubmitInfo_ReportID);
            using (MemoryStream templeteStream = new MemoryStream())
            {
                templeteStream.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                book = new NPOI.HSSF.UserModel.HSSFWorkbook(templeteStream);
            }

            var sheet = book.GetSheetAt(0);
            var row = sheet.GetRow(4);
            if (dt.Rows.Count - 1 > 0)//如果总记录数大于1，则要新增加行，因为模版中已经有一空行。所以当记录只有一条时，不用新增。
                sheet.InsertRow(5, dt.Rows.Count - 1, row);

            int startIndex = 4;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.GetRow(startIndex);
                row.Cells[0].SetCellValue(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] != DBNull.Value)
                    {
                        //判断数据的真实类型 
                        if (dt.Rows[i][j].GetType() == typeof(string))
                            row.Cells[j].SetCellValue((string)dt.Rows[i][j]);
                        if (dt.Rows[i][j].GetType() == typeof(int))
                        {
                            if (Convert.ToInt32(dt.Rows[i][j]) != 0)
                            {
                                row.Cells[j].SetCellValue(Convert.ToInt32(dt.Rows[i][j]).ToString());
                            }

                        }

                        if (dt.Rows[i][j].GetType() == typeof(long))
                        {
                            if (Convert.ToInt64(dt.Rows[i][j]) != 0)
                            {
                                row.Cells[j].SetCellValue(Convert.ToInt64(dt.Rows[i][j]).ToString());
                            }

                        }

                        if (dt.Rows[i][j].GetType() == typeof(double) || dt.Rows[i][j].GetType() == typeof(decimal))
                        {
                            if (Convert.ToDouble(dt.Rows[i][j]) != 0)
                            {
                                row.Cells[j].SetCellValue(Convert.ToDouble(dt.Rows[i][j]).ToString("0.00"));
                            }

                        }

                    }
                }

                startIndex++;
            }


            //处理 单位和期间
            T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);

            string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                //if (b1 && b2 && b3 && b4 && b5) break;
                IRow irow = sheet.GetRow(i);
                for (int j = 0; j < irow.PhysicalNumberOfCells; j++)
                {
                    //if (b1 && b2 && b3 && b4 && b5) break;
                    if (irow.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
                    switch (irow.Cells[j].StringCellValue)
                    {
                        case "{ZHDW}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("填报单位：" + organize.Organization_Name);
                                    //b1 = true;
                                }
                                break;
                            }
                        case "{QJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                //b2 = true;
                                break;
                            }
                        case "{DWFZR}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
                                    //b3 = true;

                                }
                                break;
                            }
                        case "{ZBR}":
                            {
                                irow.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
                                //b4 = true;
                                break;
                            }
                        case "{BCRQ}":
                            {
                                irow.Cells[j].SetCellValue("报出日期：" + sbrq);
                                //b5 = true;
                                break;
                            }
                    }
                }
            }


            MemoryStream stream = new MemoryStream();
            book.Write(stream);
            return stream;
        }

        public void SaveNYCYHLTQYNB(T_NYCYHLTQYNB model, out string msg)
        {
            msg = string.Empty;
            List<int> eleCountList = new List<int>();
            eleCountList.Add(model.T_NYCYHLTQYNB1_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB2_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB3_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB4_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB5_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB6_List.Count);
            eleCountList.Add(model.T_NYCYHLTQYNB7_List.Count);
            if (eleCountList.Distinct().Count() > 1)
            {
                msg = "保持失败，请确认所有表的行数是否一致";
                return;
            }
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    trans.Delete<T_NYCYHLTQYNB1>(T_NYCYHLTQYNB1._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB2>(T_NYCYHLTQYNB2._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB3>(T_NYCYHLTQYNB3._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB4>(T_NYCYHLTQYNB4._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB5>(T_NYCYHLTQYNB5._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB6>(T_NYCYHLTQYNB6._.SubmitInfoID == model.SubmitInfoID);
                    trans.Delete<T_NYCYHLTQYNB7>(T_NYCYHLTQYNB7._.SubmitInfoID == model.SubmitInfoID);
                    foreach (var item in model.T_NYCYHLTQYNB1_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB2_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB3_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB4_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB5_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB6_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    foreach (var item in model.T_NYCYHLTQYNB7_List)
                    {
                        item.SubmitInfoID = model.SubmitInfoID;
                        item.Detach();
                        trans.Save(item);
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    trans.Rollback();
                }
            }
        }

        public DataTable GetNYCYHLTQYJBDT(WhereClip where, QueryBase query)
        {
            Field[] fields = { T_NYCYHLTQYJB._.All };
            var section = DB.From<T_NYCYHLTQYJB>().Where(where).Select(fields);
            return section.ToTablePage(query);
        }

        public void SaveNYCYHLTQYJB(List<T_NYCYHLTQYJB> dataList, out string msg)
        {
            msg = string.Empty;
            if (dataList.Count == 0)
            {
                msg = "明细数量不能为空";
                return;
            }
            int submitInfoID = dataList[0].SubmitInfoID; ;
            T_SubmitInfo submitInfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            if (submitInfo.SubmitInfo_State == "1")
            {
                msg = "不能修改已上报的数据";
                return;
            }
            using (DbTrans trans = DB.BeginTrans())
            {
                try
                {
                    trans.Delete<T_NYCYHLTQYJB>(T_NYCYHLTQYJB._.SubmitInfoID == submitInfoID);
                    foreach (var item in dataList)
                    {
                        item.Detach();
                        trans.Save(item);
                    }
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
        }


        public MemoryStream GetNYCYHLTQYJBStream(int submitInfoID, string Organization_Level)
        {
            WhereClip where = WhereClip.All;

            if (!string.IsNullOrEmpty(Organization_Level))
                where = where && T_Organization._.Organization_Level == Organization_Level;
            Field[] fields = {T_NYCYHLTQYJB._.All, new Field("CASE ORGANIZATION_LEVEL WHEN '1' THEN '国家级' WHEN '2' THEN '省级' WHEN '3' THEN '市级' ELSE '' END AS 'LTQYDJ'")
                                  };
            //DataTable dt = DB.From<T_NYCYHLTQYJB>().Where(T_NYCYHLTQYJB._.SubmitInfoID == submitInfoID).Select(fields).ToDataTable();
            //List<T_NYCYHLTQYJB> ListTemp = (List<T_NYCYHLTQYJB>)DB.From<T_NYCYHLTQYJB>()
            //    .LeftJoin<T_Organization>(T_NYCYHLTQYJB._.QYMC == T_Organization._.Organization_Name)
            //    .Select(fields).Where(T_NYCYHLTQYJB._.SubmitInfoID == submitInfoID&&where).ToList();
            DataTable ListTemp =DB.From<T_NYCYHLTQYJB>()
                .LeftJoin<T_Organization>(T_NYCYHLTQYJB._.QYMC == T_Organization._.Organization_Name)
                .Select(fields).Where(T_NYCYHLTQYJB._.SubmitInfoID == submitInfoID && where).ToDataTable();
           ListTemp.Columns.Remove("ID");  //移除表中id列。
           ListTemp.Columns.Remove("SubmitInfoID");  //移除表中submitid列。

            HSSFWorkbook book = null;
            T_SubmitInfo submitinfo = DB.Single<T_SubmitInfo>(T_SubmitInfo._.SubmitInfo_ID == submitInfoID);
            T_Report report = DB.Single<T_Report>(T_Report._.Report_ID == submitinfo.SubmitInfo_ReportID);
            using (MemoryStream templeteStream = new MemoryStream())
            {
                templeteStream.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                book = new NPOI.HSSF.UserModel.HSSFWorkbook(templeteStream);
            }

            var sheet = book.GetSheetAt(0);
            var row = sheet.GetRow(4);
            if (ListTemp.Rows.Count - 1 > 0)//如果总记录数大于1，则要新增加行，因为模版中已经有一空行。所以当记录只有一条时，不用新增。
                sheet.InsertRow(5, ListTemp.Rows.Count - 1, row);
            int startIndex = 4;

            foreach (DataRow obj in ListTemp.Rows)
            {
                row = sheet.GetRow(startIndex);


                //i
                for (int i = 0; i < obj.ItemArray.Length; i++)
                {
                    if (obj[i]!=null)
                    {
                        row.Cells[i].SetCellValue(obj[i].ToString());
                    }
                }

                

                //if (!string.IsNullOrWhiteSpace(obj.QYMC))
                //{
                //    row.Cells[0].SetCellValue(obj.QYMC.ToString());
                //}
                //if (obj.XSSR_LJ != null) //销售收入累计
                //{
                //    row.Cells[1].SetCellValue(obj.XSSR_LJ.Value.ToString());
                //}
                //if (obj.XSSR_JJ != null) //销售收入加减
                //{
                //    row.Cells[2].SetCellValue(obj.XSSR_JJ.Value.ToString());
                //}
                //if (obj.ZJZ_LJ != null) //增加值累计
                //{
                //    row.Cells[3].SetCellValue(obj.ZJZ_LJ.Value.ToString());
                //}
                //if (obj.ZJZ_JJ != null) //增加值加减
                //{
                //    row.Cells[4].SetCellValue(obj.ZJZ_JJ.Value.ToString());
                //}
                ////利润
                //if (obj.LRZE_LJ != null) //利润总额总计
                //{
                //    row.Cells[5].SetCellValue(obj.LRZE_LJ.Value.ToString());
                //}
                ////利润总额加减
                //if (obj.LRZE_JJ != null)
                //{
                //    row.Cells[6].SetCellValue(obj.LRZE_JJ.Value.ToString());
                //}
                ////实交累计
                //if (obj.SJSJ_LJ != null)
                //{
                //    row.Cells[7].SetCellValue(obj.SJSJ_LJ.Value.ToString());
                //}
                ////实交增加值
                //if (obj.SJSJ_JJ != null)
                //{
                //    row.Cells[8].SetCellValue(obj.SJSJ_JJ.Value.ToString());
                //}
                ////出口创汇 累计
                //if (obj.CKCH_LJ != null)
                //{
                //    row.Cells[9].SetCellValue(obj.CKCH_LJ.Value.ToString());
                //}
                ////出口创汇 增减
                //if (obj.CKCH_JJ != null)
                //{
                //    row.Cells[10].SetCellValue(obj.CKCH_JJ.Value.ToString());
                //}
                ////职工人数
                //if (obj.ZGRS_LJ != null)
                //{
                //    row.Cells[11].SetCellValue(obj.ZGRS_LJ.Value.ToString());
                //}
                ////职工人数 增减
                //if (obj.ZGRS_JJ != null)
                //{
                //    row.Cells[12].SetCellValue(obj.ZGRS_JJ.Value.ToString());
                //}
                ////劳动者报酬
                //if (obj.LDZBC_LJ != null)
                //{
                //    row.Cells[13].SetCellValue(obj.LDZBC_LJ.Value.ToString());
                //}
                ////劳动者报酬 增减
                //if (obj.LDZBC_JJ != null)
                //{
                //    row.Cells[14].SetCellValue(obj.LDZBC_JJ.Value.ToString());
                //}
                ////固定资产投资
                //if (obj.GDZCTZ_LJ != null)
                //{
                //    row.Cells[15].SetCellValue(obj.GDZCTZ_LJ.Value.ToString());
                //}
                ////固定资产投资 增减
                //if (obj.GDZCTZ_JJ != null)
                //{
                //    row.Cells[16].SetCellValue(obj.GDZCTZ_JJ.Value.ToString());
                //}
                ////招商引资小计
                //if (obj.ZSYZ_XJ != null)
                //{
                //    row.Cells[17].SetCellValue(obj.ZSYZ_XJ.Value.ToString());
                //}
                ////
                //if (obj.ZSYZ_NZ != null)
                //{
                //    row.Cells[18].SetCellValue(obj.ZSYZ_NZ.Value.ToString());
                //}
                ////
                //if (obj.ZSYZ_WZ != null)
                //{
                //    row.Cells[19].SetCellValue(obj.ZSYZ_WZ.Value.ToString());
                //}
                ////
                //if (obj.ZSYZ_JJ != null)
                //{
                //    row.Cells[20].SetCellValue(obj.ZSYZ_JJ.Value.ToString());
                //}
                ////银行 累计
                //if (obj.YHDK_LJ != null)
                //{
                //    row.Cells[21].SetCellValue(obj.YHDK_LJ.Value.ToString());
                //}
                ////银行 其中农发
                //if (obj.YHDK_NFH != null)
                //{
                //    row.Cells[22].SetCellValue(obj.YHDK_NFH.Value.ToString());
                //}
                ////基地建设
                //if (obj.JDJS_XJ != null)
                //{
                //    row.Cells[23].SetCellValue(obj.JDJS_XJ.Value.ToString());
                //}
                ////基地建设 自建基地
                //if (obj.JDJS_ZJJD != null)
                //{
                //    row.Cells[24].SetCellValue(obj.JDJS_ZJJD.Value.ToString());
                //}
                ////基地建设 联系基地
                //if (obj.JDJS_LXJD != null)
                //{
                //    row.Cells[25].SetCellValue(obj.JDJS_LXJD.Value.ToString());
                //}
                ////联系农户
                //if (obj.LXNH != null)
                //{
                //    row.Cells[26].SetCellValue(obj.LXNH.Value.ToString());
                //}
                ////龙头企业级别
                //if (obj.LTQYDJ != null)
                //{
                //    row.Cells[27].SetCellValue(obj.LTQYDJ.Value.ToString());
                //}

                startIndex++;
            }
            //处理 单位和期间
            T_Organization organize = DB.Single<T_Organization>(T_Organization._.Organization_ID == submitinfo.SubmitInfo_OrganizationID);

            string sbrq = string.IsNullOrEmpty(submitinfo.SubmitInfo_SubmitDate) ? DateTime.Now.Year.ToString("0000") + "年" + DateTime.Now.Month.ToString("00") + "月" + DateTime.Now.Day.ToString("00") + "日" : Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Year.ToString("0000") + "年" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Month.ToString("00") + "月" + Convert.ToDateTime(submitinfo.SubmitInfo_SubmitDate).Day.ToString("00") + "日";
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                //if (b1 && b2 && b3 && b4 && b5) break;
                IRow irow = sheet.GetRow(i);
                for (int j = 0; j < irow.PhysicalNumberOfCells; j++)
                {
                    //if (b1 && b2 && b3 && b4 && b5) break;
                    if (irow.Cells[j].CellType != NPOI.SS.UserModel.CellType.STRING) continue;
                    switch (irow.Cells[j].StringCellValue)
                    {
                        case "{ZHDW}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("填报单位:" + organize.Organization_Name);
                                    //b1 = true;
                                }
                                break;
                            }
                        case "{QJ}":
                            {
                                switch (submitinfo.SubmitInfo_Cycle)
                                {
                                    case "01":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}月", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "02":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年{1}季度", submitinfo.SubmitInfo_Year, submitinfo.SubmitInfo_Month));
                                        break;
                                    case "03":
                                        irow.Cells[j].SetCellValue(string.Format("{0}年", submitinfo.SubmitInfo_Year));
                                        break;
                                }
                                //b2 = true;
                                break;
                            }
                        case "{DWFZR}":
                            {
                                if (organize != null)
                                {
                                    irow.Cells[j].SetCellValue("单位负责人：" + organize.Organization_Contact);
                                    //b3 = true;

                                }
                                break;
                            }
                        case "{ZBR}":
                            {
                                irow.Cells[j].SetCellValue("制表人：" + submitinfo.SubmitInfo_CreatorName);
                                //b4 = true;
                                break;
                            }
                        case "{BCRQ}":
                            {
                                irow.Cells[j].SetCellValue("报出日期：" + sbrq);
                                //b5 = true;
                                break;
                            }
                    }
                }
            }


            MemoryStream stream = new MemoryStream();
            book.Write(stream);
            return stream;
        }

        //计算非模板类报表的记录数，用于控制，空表不能上报。
        public int CountRow<T>(string SubmitInfoId) where T : Entity
        {
            //判断有无指标数据，没有指标数据，不能上报。
            WhereClip where = WhereClip.All;
            where = where && new Field("SubmitInfoID") == SubmitInfoId;
            int icount = DB.Count<T>(where);

            return icount;
        }

        public int CountRow(Type entityType, string SubmitInfoId)
        {
            string tableName = entityType.Name;

            QueryCreator qc = QueryCreator.NewCreator();
            qc = qc.From(tableName).AddWhere(new Field("SubmitInfoID") == SubmitInfoId).AddField(new Field("count(1)"));
            int icount = DB.From(qc).ToScalar<int>();
            return icount;
        }

        /// <summary>
        /// 获取所有报表名列表,查询单位上报情况的页面使用
        /// </summary>
        /// <param name="user"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetReportNameList(CurrentUser user)
        {

            DataTable dtResult;

            string temp = "";
            WhereClip where = WhereClip.All;

            //获取当前用户的所在部门，如果是龙头企业，则应过滤掉只下发给管理部门的报表
            T_Organization organization = GetEntity<T_Organization>(T_Organization._.Organization_ID == user.UserInfo.User_OrganizationID);

            if (organization.Organization_Type == "1")
            {
                where = where && T_Report._.Report_Xflx == "1";
            }

            Field[] f = { T_Report._.Report_ID.As("Report_ID"), 
                                new Field("report_name +case report_cycle when '01' then ' 【月报】' when '02' then ' 【季报】' else  ' 【年报】' end").As("Report_Name"),
                                new Field("'/Content/32/bb.png'").As("Report_Image"),T_Report._.Report_Cycle.As("Report_Cycle") };
            DataTable dt = DB.From<T_Report>().Select(f).Where(where).OrderBy(T_Report._.Report_Xflx.Asc && T_Report._.Report_Cycle.Desc).ToDataTable();

            dtResult = dt.Clone();
            foreach (DataRow dr in dt.Rows)
            {
                // DataTable1.Rows.Add(obj);   
                dtResult.ImportRow(dr);
            }

            return dtResult;
        }



    }



    public class colModel
    {
        public string label { get; set; }
        public string name { get; set; }
    }


    public class LTQYNBColModels
    {
        public List<colModel> colModels1 { get; set; }
        public List<colModel> colModels2 { get; set; }
        public List<colModel> colModels3 { get; set; }
        public List<colModel> colModels4 { get; set; }
        public List<colModel> colModels5 { get; set; }
        public List<colModel> colModels6 { get; set; }
        public List<colModel> colModels7 { get; set; }
    }

    public enum OpType
    {
        /// <summary>
        /// 大于
        /// </summary>
        GT,
        /// <summary>
        /// 等于
        /// </summary>
        EQ,
        /// <summary>
        /// 小于
        /// </summary>
        LT,
        /// <summary>
        /// 大于或等于
        /// </summary>
        GTorEQ,
        /// <summary>
        /// 小于或等于
        /// </summary>
        LTorEQ
    }

}
